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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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

Forum statistics

Threads
1,141,916
Messages
5,709,327
Members
421,629
Latest member
RLRobinson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top