Macro help - selecting rows after custom filter

Excel >> Macro help - selecting rows after custom filter

Hello

I have recorded a macro to automatically import a file and then run a number
of filters through it and to display the results in a separate worksheet.

I do not have any Visual Basic knowledge, so am just using the "record
macro" function. So far so good, but now I've run into a problem.

How do I edit my macro so that it can automatically replace the contents of
cells in a particular column in as many rows as are required?

EXAMPLE:

Let's say the macro runs a custom filter in Column X to show all the rows
which have a value of either "A" orf "B" in that column. Now, what I want
the macro to do is to automatically select all those rows and replace the
contents of all the cells in Column Y with the value "1".

How do I get the macro to select all the necessary rows given that the
number of rows will be different every week I run the macro? That is, when
I record the macro and run the custom filter, it may truncate my list to 10
rows of data. When I then go to use the macro the following week on a newly
imported file, the truncated list may have 17 rows of data. The following
week, 5 rows of data, and so on.

I guess this gets down to Excel navigation and the ability to automatically
select only the displayed rows of a particular column.

I hope this all makes sense....?

Any help would be most appreciated!

Thanks,

Joe.

Excel246

Excel >> Macro help - selecting rows after custom filter Hi Joe

The example here copy all visible data to a new sheet
http://www.rondebruin.nl/copy5.htm#AutoFilter

We use
WS.AutoFilter.Range.Copy

But you only want to replace the data in one column on the destination sheet(existing sheet)
Am I correct

Let me know then I will help you

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Hello
>
> I have recorded a macro to automatically import a file and then run a number
> of filters through it and to display the results in a separate worksheet.
>
> I do not have any Visual Basic knowledge, so am just using the "record
> macro" function. So far so good, but now I've run into a problem.
>
> How do I edit my macro so that it can automatically replace the contents of
> cells in a particular column in as many rows as are required?
>
> EXAMPLE:
>
> Let's say the macro runs a custom filter in Column X to show all the rows
> which have a value of either "A" orf "B" in that column. Now, what I want
> the macro to do is to automatically select all those rows and replace the
> contents of all the cells in Column Y with the value "1".
>
> How do I get the macro to select all the necessary rows given that the
> number of rows will be different every week I run the macro? That is, when
> I record the macro and run the custom filter, it may truncate my list to 10
> rows of data. When I then go to use the macro the following week on a newly
> imported file, the truncated list may have 17 rows of data. The following
> week, 5 rows of data, and so on.
>
> I guess this gets down to Excel navigation and the ability to automatically
> select only the displayed rows of a particular column.
>
> I hope this all makes sense....?
>
> Any help would be most appreciated!
>
> Thanks,
>
> Joe.
>

Excel >> Macro help - selecting rows after custom filter Hi Ron

Thank you for your help! Much appreciate it.

Yes, I only need to replace the data in one column. This seemed easy enough
at first, but then I realised that the number of rows will change each week.

Joe.

> Hi Joe
>
> The example here copy all visible data to a new sheet
> http://www.rondebruin.nl/copy5.htm#AutoFilter
>
> We use
> WS.AutoFilter.Range.Copy
>
> But you only want to replace the data in one column on the destination
> sheet(existing sheet)
> Am I correct
>
> Let me know then I will help you
>
>

Excel >> Macro help - selecting rows after custom filter Hi Joe

Must go now but will reply this evening

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Hi Ron
>
> Thank you for your help! Much appreciate it.
>
> Yes, I only need to replace the data in one column. This seemed easy enough
> at first, but then I realised that the number of rows will change each week.
>
> Joe.
>
>

>
>> Hi Joe
>>
>> The example here copy all visible data to a new sheet
>> http://www.rondebruin.nl/copy5.htm#AutoFilter
>>
>> We use
>> WS.AutoFilter.Range.Copy
>>
>> But you only want to replace the data in one column on the destination
>> sheet(existing sheet)
>> Am I correct
>>
>> Let me know then I will help you
>>
>>
>

Excel >> Macro help - selecting rows after custom filter

> Hello
>
> I have recorded a macro to automatically import a file and then run a
> number of filters through it and to display the results in a separate
> worksheet.
>
> I do not have any Visual Basic knowledge, so am just using the "record
> macro" function. So far so good, but now I've run into a problem.
>
> How do I edit my macro so that it can automatically replace the contents
> of cells in a particular column in as many rows as are required?
>
> EXAMPLE:
>
> Let's say the macro runs a custom filter in Column X to show all the
> rows which have a value of either "A" orf "B" in that column. Now, what
> I want the macro to do is to automatically select all those rows and
> replace the contents of all the cells in Column Y with the value "1".
>
> How do I get the macro to select all the necessary rows given that the
> number of rows will be different every week I run the macro? That is,
> when I record the macro and run the custom filter, it may truncate my
> list to 10 rows of data. When I then go to use the macro the following
> week on a newly imported file, the truncated list may have 17 rows of
> data. The following week, 5 rows of data, and so on.
>
> I guess this gets down to Excel navigation and the ability to
> automatically select only the displayed rows of a particular column.
>
> I hope this all makes sense....?
>
> Any help would be most appreciated!
>
> Thanks,
>
> Joe.

Try looking here:
http://nerds-central.blogspot.com/2008/01/excel-filter-report-scripting-
macro.html

--
http://nerds-central.blogspot.com/search/label/Baby%20Steps

Excel >> Macro help - selecting rows after custom filter Hi Joe

You can run this macro on your new sheet

Maybe you want to clear column Y first so you only see the 1 if there is A or B in X
Let me know if you want that

Sub AutoFilterTest()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Name of the worksheet with the data
Set WS = ActiveSheet

'Set filter range : All data in X
Set rng = WS.Range("X1:X" & Rows.Count)

'Firstly, remove the AutoFilter
WS.AutoFilterMode = False

rng.AutoFilter Field:=1, Criteria1:="=A", Operator:=xlOr, Criteria2:="=B"

WS.AutoFilter.Range.Offset(0, 1).Value = 1

'Close AutoFilter
WS.AutoFilterMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Hi Joe
>
> Must go now but will reply this evening
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>

>> Hi Ron
>>
>> Thank you for your help! Much appreciate it.
>>
>> Yes, I only need to replace the data in one column. This seemed easy enough
>> at first, but then I realised that the number of rows will change each week.
>>
>> Joe.
>>
>>

>>
>>> Hi Joe
>>>
>>> The example here copy all visible data to a new sheet
>>> http://www.rondebruin.nl/copy5.htm#AutoFilter
>>>
>>> We use
>>> WS.AutoFilter.Range.Copy
>>>
>>> But you only want to replace the data in one column on the destination
>>> sheet(existing sheet)
>>> Am I correct
>>>
>>> Let me know then I will help you
>>>
>>>
>>