# [VBA] Faster way to find last used row in range (.End(xlUp))

This topic is 2072 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

I wrote this function:

'Given a range of a few cells in a row, will return a range that starts at 'myRange'
'and goes until the final row that actually is being used that is contained within 'myRange'.
'I.E., given "B6:F6", will return (B6:F245) if B245 to F245 contains the last value in the columns "B:F"
'
'If 'myRange' only contains a single row, it will be resized to go from 'myRange' to the last row used in
'the entire worksheet, within the columns that 'myRange' refers to.
Function GetUsedRange(myRange As range) As range

Dim oldActiveSheet As Worksheet
Set oldActiveSheet = ActiveSheet

'We need to disable events, or when we make this sheet active we could go into an infinite loop
'if Worksheet_Activate() or Worksheet_Deactivate() calls this function.
Dim eventsWereEnabled As Boolean
eventsWereEnabled = Application.enableEvents
Application.enableEvents = False

Dim screenWasUpdating As Boolean
screenWasUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False

Dim mySheet As Worksheet
Set mySheet = myRange.Worksheet

'We have to make this worksheet the active one, if we're to use "range.End(xlUp).row"
mySheet.Activate

Dim lastUsedRow As Long
lastUsedRow = myRange.row

Dim lastColumnInRange As Long
lastColumnInRange = myRange.Column

'Limit the search just to the range contained in 'myRange'
Dim lastRowToCheckFor As Long
lastRowToCheckFor = myRange.row + myRange.rows.count

'But if 'myRange' only contains a single row, go from that row
'until the end of the worksheet.
If myRange.rows.count = 1 Then
lastRowToCheckFor = mySheet.rows.count
End If

Dim lastRowInColumn As Long

'Check all the column last rows, and get the truly last row.
Dim columnCell As range
For Each columnCell In myRange.Columns
lastRowInColumn = Cells(lastRowToCheckFor, columnCell.Column).End(xlUp).row
lastUsedRow = Max(lastUsedRow, lastRowInColumn)

lastColumnInRange = columnCell.Column
Next

Set GetUsedRange = mySheet.range(mySheet.Cells(myRange.row, myRange.Column), mySheet.Cells(lastUsedRow, lastColumnInRange))

'Return the old active worksheet to being active again.
oldActiveSheet.Activate

'Restore enabling events to its original state.
Application.enableEvents = eventsWereEnabled
Application.ScreenUpdating = screenWasUpdating

End Function


...and it can be noticeably slow.

I think the cause of the slowness is range.End(xlUp) once for every column in the range passed in. What I want is to pass in a range of cells (which I assume are on the same row), and then find the last row that contains data within those columns.

In images:

Is there a faster way to do this?

##### Share on other sites

This made me think of bounding boxes, so I did a quick google on

spreadsheet bounding box used cells

The first link showed some promise with a macro for finding the last nonempy cell in a column. I wasn't sure if VBA was a requirement or if you could switch over to using macros. Also, I think this might be for Open Office, so it might need some adapting to work in Excel.

=MAX(ROW(A1:A65536)*NOT(ISBLANK(A1:A65536))) [Ctrl+Shift+Enter]
//gives the last non-empty row of column A


I don't think this solves your problem, but hopefully it gives you some inspiration. :)

And as with all performance issues, profile it first! You might find that turning the events off and back on is where the slowness is happening. To profile this, you might just get a timestamp and cram it into a few columns on your spreadsheet for debug purposes. include a label, start time, and stop time.

- Eck

##### Share on other sites

A quick search says that you should be able to use Range.Find to track down the cell. Something like:

Set result = myRange.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

No idea if that will be any quicker.

• 21
• 12
• 9
• 17
• 13