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

  • 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.)

  • Re: Find Last & First Used Row & Column -- Yet Another Approach


    Hi Tom,


    Another great info page for ozgriders.


    To add, I also use this method:


    Code
    1. With ActiveSheet.UsedRange
    2. lfirstrow = .Row
    3. lfirstcol = .Column
    4. llastrow = .Rows(UBound(.Value)).Row
    5. llastcol = .Columns(UBound(.Value, 2)).Column
    6. 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. ;)

  • Re: Find Last & First Used Row & Column -- Yet Another Approach


    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.