Find Last & First Used Row & Column -- Yet Another Approach

[thomach]

I've seen many techniques (VBA code and formulas) for getting the Last Row or Column of the active sheet's used range, but I thought I'd add another that I don't think I've seen elsewhere. I like this versin because it's concise adn because it also can easily pull the first used row and column as well as the last ones. It makes use of the VBA ability to identify the element of a range by an index number. (1) refers to the top left (first) cell in the range and the last cell has an index number equal to the number of cells in the range.

Hence this code. (The MsgBox lines can be discarded, they are included only to make it easy to check the code on a worksheet to see if it is returning what it should.)

Sub FindLastFirstUsedRowColumn() 
    Dim UsedRng As Range 
    Dim FirstRow As Long, LastRow As Long, FirstCol As Long, LastCol As Long 
     
    Set UsedRng = ActiveSheet.UsedRange 
     
    FirstRow = UsedRng(1).Row 
    FirstCol = UsedRng(1).Column 
    LastRow = UsedRng(UsedRng.Cells.Count).Row 
    LastCol = UsedRng(UsedRng.Cells.Count).Column 
     
    MsgBox "First used row is: " & FirstRow 
    MsgBox "First used column is: " & FirstCol 
    MsgBox "Last used row is: " & LastRow 
    MsgBox "Last used column is: " & LastCol 
     
End Sub 

[Reafidy]

Hi Tom,

Another great info page for ozgriders.

To add, I also use this method:

With ActiveSheet.UsedRange 
    lfirstrow = .Row 
    lfirstcol = .Column 
    llastrow = .Rows(UBound(.Value)).Row 
    llastcol = .Columns(UBound(.Value, 2)).Column 
End With 

I have a little personal issue with using set, I prefer not to set a range object unless I have too. Its slighty unjustified/justified but I cant shake it.

[thomach]

And thank you! Another great supplement that you provided. As Derk (the Professor) says, "Every day I learn something new about Excel." And Oz is a great place to let that happen.