Find the absolute last used row - even when that row is hidden

[EchoPassenger]

I am working on an import macro procedure where multiple external files are opened, a set range is copied, then the data is pasted to a master sheet.

I was using .End(xlUp) to find the last used cell in column E to paste the next lot of data, but I notice it is thrown off if the master sheet is filtered and it returns the last used visible row.

What is the best way to find the absolute last used row even when a sheet is in a filtered state?

For example, if my sheet has 500 used rows, but a filter is in place and only 100 of them are showing, I still want the macro to be able to find that the last row is 500.

[TMS]

If column E is fully populated, you could try using COUNTA. Or maybe create a Named Range for column E and use the row count. Or you could try making the data into Structured Table.

[shg]

I don't remember ever using this ...

Please Login or Register  to view this content.

[EchoPassenger]

If column E is fully populated, you could try using COUNTA. Or maybe create a Named Range for column E and use the row count. Or you could try making the data into Structured Table.

Yeah I thought of that, and technically column E should always be populated down to the last row, hence why I was using it, but I have to allow for a user accidentally clearing a cell in that column and I really don't want to risk overwriting anything.

I don't remember ever using this ...

Please Login or Register  to view this content.

Well that seems to work in all 3 scenarios - with filtered rows, with no filtered rows and with no filter applied at all. I can only test it in 2007 at the moment, but it will need to work in 2013 and probably 2016 as well. Would there be any issues with the later versions of Excel as I know there are increased filter options in those?

Is there a way of limiting this to a specific range of columns ?

[shg]

Is there a way of limiting this to a specific range of columns ?

Please Login or Register  to view this content.

[EchoPassenger]

Ah of course, sorry.

[shg]

You're welcome.