Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1

    Unanswered: Best way to return last row with data?

    When selecting a range in VBA, is there a relatively simple way to restrict the range to only the last row showing data, then return properties such as row count etc?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Oct 2003
    Something like?

        Dim myRow As Long
        myRow = Range("A1:A" & Range("A65536").End(xlUp).Row).Count
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Hi Teddy,
    various ways of doing this my favorite at the mo is

    Sub GetLastRow()
        Dim rngLast As Range
        'try and get the last cell with data in
        Set rngLast = Cells.Find("*", searchdirection:=xlPrevious)
        'check for existance
        If Not rngLast Is Nothing Then
            'get the lastrow
            Debug.Print rngLast.Row
            'get the cell address
            Debug.Print rngLast.Address
            'select all the rows between 1 and end
            Range(Cells(1, 1), rngLast).EntireRow.Select
        End If
    End Sub
    anything else that you need?

  4. #4
    Join Date
    Feb 2004
    Here's two ways I like to use. With the 'GetLastRow' Function you can call it from any procedure in your project, so long as it is a public function as shown. One thing to consider about getting the last row, if you use end(xldown).row to get the row then it will not find the last row if there are blanks in the list. None of the examples used xldown, just thought i'd mention it.

    ''Set topCell = .Range("A2")
    ''    r = GetLastRow(topCell) + 1
    Function GetLastRow(topCell As Range)
      Dim maxRow As Long
    'Determine the last Used Row
      With topCell.Parent.UsedRange
            maxRow = .Cells(.Cells.Count).Row + 1
      End With
      With topCell
       GetLastRow = .Parent.Cells(maxRow, _
      End With
    End Function
    'Second Way:
    ''''This assumes you will have data in Col A 
    ' Determine the last used row
      If ActiveSheet.Range("A2") <> "" Then
            Set botcell = ActiveSheet.Range("A65536")
            Set Lastcell = botcell.End(xlUp)
            r = Lastcell.Row + 1
            r = 2
      End If
    '''''USE r in cell r1c1 address, after setting R''''''
    'With ActiveSheet
    '        .Cells(r, 4).Select
    'End With
    Both these examples have "+1" after getting the last row. This is so I can get the next row for entering data. If you want the actual last row just drop the +1, and in example 2 you wouldn't need the Else condition in the if statement either.

    Last edited by savbill; 01-29-05 at 06:45.


Posting Permissions

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