Find last row in filtered range

[robertvdb]

I have a list with cities in USA and Canada. Now I need some VBA code to identify the last row in this list (row 23 in this case), however AFTER the filter has been applied.

So if I filter the list on "USA", the result of the VBA should still be row 23, not row 18.

Can anybody help ? See the enclosed screen capture.

 

[Fluff]

Will col A ever have blank cells?

 

[robertvdb]

No blank cells in any column.

 

[Fluff]

In that case you could use

   LastRow = Application.CountA(Range("A:A"))

 

[robertvdb]

Thanks !

I have a similar case where the first row of the data is the 6th row, so the last row here would be row 27. Is there any solution for this ?

 

[Fluff]

Just add 5 to the result.

 

[robertvdb]

yes, but what if the starting row is dynamic ? Sometimes it's the 6th, sometimes it's another...

 

[Fluff]

Then you will need to find the row of data, add that number to the result less 1

 

[robertvdb]

thanks. Will give it a try...

 

[Fluff]

Ok, any problems, just shout.