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

If you post the macro it would be easier to help you.

To find the last row or column you use either
..end(xlup), .end(xldown), .end(xltoleft), or .end(xltoright). Which one you
use depends if you have any blank cells in the data. These commands behave
the same way as the keyboard shift-Arrow behave. If you ae in a blank cell
they find the first non-blank cell. Or if you are in a cell with data, the
find the last cell with data.


Other useful commands are Rows.count (equal 65536 in excel 2003) and
column.count (equals 256 in excel 2003)


to find last row use
Lastrow = Range("A" & Rows.count).end(xlup).row
This says to go to row 65536 in column A and search until you find a cell
with data

Likewise
Lastcol = cells(1,Columns.count).end(xltoleft).column


"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 either "A" or "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.