Best way to return last row with data?

Teddy · #1 (**permalink**) 01-28-05, 10:13

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?

shades · #2 (**permalink**) 01-28-05, 10:57

Something like?

Code:

    Dim myRow As Long
    myRow = Range("A1:A" & Range("A65536").End(xlUp).Row).Count

DavidCoutts · #3 (**permalink**) 01-28-05, 10:58

Hi Teddy,
various ways of doing this my favorite at the mo is

Code:

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?
dave

savbill · #4 (**permalink**) 01-29-05, 05:40

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.

Code:

 
'GetLastRow()
'''''CALL'''''''
''Set topCell = .Range("A2")
''    r = GetLastRow(topCell) + 1
'''''CALL'''''''
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, _
       .Column).End(xlUp).Row
  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
  Else
        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.

/

Internet Services

Web Development

Digital Marketing

Consumer Tech