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.

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.
--
Gary''s Student - gsnu2007c


"Joe" wrote:

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.

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.

See your other thread

--

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


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