Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

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

  1. #1
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.)
    VB:
    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 
    
    
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,921

    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:

    VB:
    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.
    Last edited by Reafidy; July 1st, 2008 at 12:15.

  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Possible Answers

  1. Replies: 12
    Last Post: August 4th, 2006, 23:24
  2. Replies: 2
    Last Post: May 12th, 2006, 10:48
  3. Best Approach to Compare Data
    By JMAN in forum EXCEL HELP
    Replies: 2
    Last Post: May 9th, 2006, 23:24
  4. Question how to approach simulation
    By jacknijssen in forum EXCEL HELP
    Replies: 3
    Last Post: April 24th, 2003, 21:41

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno