Nice clean way to Measure/set EOD of worksheet

**MrHockey** · 11-10-2010, 12:51 AM

Hello and thank you for time and effort to review this thread.

This is something that has been bugging me for a long time. I found one of cleanest methods to measure/set the last row (EOD) of a worksheet is

Please Login or Register  to view this content.

It reports the last row reliably even if the column includes blank cells above EOD, it will ignore that and report the last 'non-vacant' cell. Other methods report the first occurance of a blank cell.

The problem is, the syntax is nasty, I would like to report the column numerically instead of using an alpha ('A...'), for example

Please Login or Register  to view this content.

This doesn't work but it would be a big improvement.

Here is an even better example

Please Login or Register  to view this content.

This would be the ultimate.

Is there a way to adapt this to measure the last column, something like this

Please Login or Register  to view this content.

**Leith Ross** · 11-10-2010, 02:19 AM

Hello MrHockey,

This looks like a 2 part question. To get the column number, instead of the letter, you can set a range object equal to the new range determined by the last cell in the column. Here is an example.

Please Login or Register  to view this content.

**DonkeyOte** · 11-10-2010, 04:52 AM

The "standard" method for determing last cell within a specific range is outlined here
(as outlined in link above use of UsedRange, xlCellTypeLastCell etc are not bullet proof)

As Leith has illustrated the use of hard wired row references should be replaced by Rows.Count given this will work across all XL versions
(65536 does not apply to XL2007 etc...)