last row on the sheet

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
Hi,

This is probably the most stupid of questions for someone with decent Excel knowledge to ask, but I never really got this working properly.

I need to retrieve the last row on the sheet that has data on it (using VBA). There are blank rows and the sheet is too large to use a loop.

Thanks in advance and sorry for the dumb question.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

The only question that is dumb is the one that isn't asked.

Code:
longVar = Range("A" & Application.Rows.Count).End(xlUp).Row

will assign the value of the last cell containing data in column A to 'longVar' (a euphemism for a variable declared as Long data-type).

Richard
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

Hi,

thanks for the help all.

Range("A" & Application.Rows.Count).End(xlUp).Row
does not always work, since the data is not neccessary in column A :wink:
but the special cells thing works like a charm.
Thanks guys!
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
Someone correct me if I'm wrong, but I think the SpecialCells method returns the last cell that has been used - even if the data has subsequently been deleted.
To see what I mean Harvey open a new workbook and press Ctrl+End and the active cell will stay at A1, now enter some data into row 65000 and delete it again. Ctrl+End now takes you to row 65000.

I generally use this method to find the last cell (or I rip the function apart and use the bit I need):
Code:
Function LastCell(ws As Worksheet) As Range
  
  Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet
  On Error Resume Next
  With ws
  ' Find the last real row
    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row
    If LastRow& = 0 Then
        LastRow& = 1
    End If

  ' Find the last real column
    LastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
    If LastCol% = 0 Then
        LastCol% = 1
    End If
  End With

' Finally, initialize a Range object variable for
' the last populated row.
  Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function

Sub Demo()

   MsgBox LastCell(Sheet1).Row

End Sub

(Taken from http://www.beyondtechnology.com/home.shtml ...
http://www.beyondtechnology.com/geeks012.shtml)
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
Hi Delmar,

thanks for the explanation. My code works fine right now, but I will keep your suggestions in mind for any future improvements :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,730
Members
410,702
Latest member
clizama18
Top