Recording Macros
Public Sub Test()
Dim LastRow As Long
Dim rng As Range
Dim cell As Range
With ActiveSheet
LastRow = .Cells(.Rows.Count, "X").End(xlUp).Row
Set rng = .Range("X1").Resize(LastRow)
rng.AutoFilter Field:=1, Criteria1:="A"
Set rng = .Range("X2").Resize(LastRow -
1).SpecialCells(xlCellTypeVisible)
rng.Value = 1
Set rng = Nothing
End With
End Sub
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Joe" wrote in message
...
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 "A" 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.
|