Using VBA to autofilter and paste over results - can't select only filtered rows.

Hello, folks. I am a lurker who has picked up most of what I have learned about VBA from forums such as these. In other words, you may talk over my head.

I am working in Excel 2007. This file has a data connection set up to import a text file and perform various functions such as filtering for certain department numbers and deleting those rows and filtering for other department numbers and replacing a number in an adjacent cell. My filter and delete section works fine.

The problem is after I filter for certain department numbers (in column K) and need to replace the value in the cell next to the dept. number (column J) with a different value. I filter, then copy the new value. I can't figure out how to make my range to paste into vary with the data imported from the text file each month as this can be any number of rows. Here is my code:

Please Login or Register  to view this content.

The problem is the

Please Login or Register  to view this content.

as it may truly only be 175 rows this month or 207 rows next month. Selecting visible cells isn't helping because if my data is only 175 rows, the filter only works for the 175 rows but leaves rows 176- 300 showing.

Thanks. I will be happy to provide more info if necessary.

The trick is to set the range specifer to include the row that you want. I don't know how you macro knows how many rows there are each month, but let's say that you set an integer variable, N, to that number. then the statement would be

Please Login or Register  to view this content.

How's that?

Here is what I did. I have to admit Googling to find a good way to identify the last row. I had looked for this before, but somehow never got to it.

Please Login or Register  to view this content.

Now I have to figure out how to make the sub move on if it doesn't find any of the department numbers it's looking for. Can you help?

For some reason, my version of Excel 2003 does not have the xlFilterValues as a valid valid option, so I can't test this, but I think the solution is to check size of the range that is returned from to Autofilter. Try substituting your first autofiler statement with the following. It checks the size of the range returned from the autofilter and stops the module if there is nothing it in.

Please Login or Register  to view this content.