Jump to content
  • Advertisement
Sign in to follow this  
Servant of the Lord

[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.

If you intended to correct an error in the post then please contact us.

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"
    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
    Set GetUsedRange = mySheet.range(mySheet.Cells(myRange.row, myRange.Column), mySheet.Cells(lastUsedRow, lastColumnInRange))
    'Return the old active worksheet to being active again.
    '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 this post

Link to post
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 this post

Link to post
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.

Edited by Adam_42

Share this post

Link to post
Share on other sites
Sign in to follow this  

  • Advertisement

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!