Recording Macros

[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 "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.

 

[Gary''s Student]

We will run down the column and test for Hidden:

Sub fix_um()
Set rr = Range("Y1:Y100")
For Each r In rr
If Not r.EntireRow.Hidden Then
r.Value = 1
End If
Next
End Sub

You need to replace the 100 with the maximum expected number of rows. That
way you won't need to change it every day.

 

[Bob Phillips]

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)

 

[Ron de Bruin]

See your other thread