last row on the sheet

[Harvey]

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.

 

[Richard Schollar]

Hi

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

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

 

[Jon von der Heyden]

Somthing like this?

Range("A65536").End(XlUp).Row.Copy

 

[agihcam]

Hi -
try;

Cells.SpecialCells(xlCellTypeLastCell).Row

 

[Harvey]

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]

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):

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]

Hi Delmar,

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