Macro help - selecting rows after custom filter

[Joe]

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.

 

[Ron de Bruin]

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

 

[Joe]

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.

 

[Ron de Bruin]

Hi Joe

Must go now but will reply this evening

 

[Ron de Bruin]

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

 

[varun aggarwal]

pls help me in creating the graph from excel dat has different number of rows everyday.
like it has 2000 line one day and 3000 oder day.
wat should i use inside macro.
actually i have to select two column and graph will be containing two lines, dats creating a problem.
it should be dynamic tell me something like we have Range("G:G")for single column tell for multiple columns

 

[Gord Dibben]

See Tushar Mehta's site for a short course on Dynamic Charting and some
sample files.

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Alternative site is Stephen Bullen's.

http://www.oaltd.co.uk/Excel/Default.htm

Download either or both FunChrt1 and FunChrt7


Gord Dibben MS Excel MVP