Using VBA to get the last row in Excel can be a surprisingly complex task. This post will explore some of the different strategies you can use.

The first thing to do is to decide what you mean by “The Last Row” – some possibilities are:

- The row Excel considers to be the last row (last row in the Used Range)
- The last row on a sheet that actually contains data
- The last row in a range that actually contains data
- The last data row in a Table ignoring the Totals row
- The last visible row containing data
- …

## Test Data

The test data is constructed to test different last row VBA strategies for ranges and tables.

- There is a Table in cells $A$4:$A$25. The Table (called Table1) has a header row and a total row.
- There is a Named Range (called NamedRange) in cells $E$4:$E$30 but $E$26:$E$30 are empty.
- Rows 5:8, 13:15, 21:25 and 40:42 are hidden so that the Table and Name Range contain more than one visible region.
- There is data in row 32 below the Table and the Named Range.
- There is formatting in the hidden row 42.

## VBA Strategies

I will look at some of the available strategies for finding the last row.

### Used Range

Because Excel internally uses a sparse matrix scheme for the cells in each worksheet (instead of holding a gigantic 16384 by 1048576 array) it has to store information for each cell that has been used. So formatted cells are considered used, as well as cells containing values and formulas. Cells remain flagged as used even when all formatting, values and formulas are removed.

Two VBA methods for working with the used range are Worksheet.UsedRange and the xlCellTypeLastCell option of SpecialCells.

' ' last row in used range ' jLastUsed = oSht.UsedRange.Rows(oSht.UsedRange.Rows.Count).Row ' ' last visible row in used range ' jLastVisibleUsed = oSht.Cells.SpecialCells(xlCellTypeLastCell).Row

For my test data jLastUsed returns 42 because there is some formatting on that row, and xlCellTypeLastCell returns 39, which is the last visible row before row 42.

### Range.End(xlDown) and Range.End(xlUp)

These VBA methods mimic pressing Ctrl and the up and down arrows.

For name ranges they skip hidden rows but stop at the row before an empty cell.:

' ' last visible cell in Named Range using End(xlUp) ' jLastVisibleRange = oSht.Range("NamedRange").Offset(oSht.Range("NamedRange").Rows.Count, 0).End(xlUp).Row ' ' last visible cell in Named Range using End(xlDown) ' jLastVisibleRange2 = oSht.Range("NamedRange").End(xlDown).Row

When using End(xlUp) you want start outside the range in an empty cell, so I used Offset to get to the first row below the range. jLastVisibleRange returns 20.

Using End(xlDown) is simpler for a Range: the code start at the first row in the range and ends at the first of the last visible row in the range that contains data and the last row before an empty cell. It also returns 20.

But for Tables End(xlUp) does NOT skip hidden rows!

' ' last row in Table using End(xlUP) - Note End(xlUp ) behaves differently for tables - includes hidden rows ' jLastInTable2 = oSht.Range("Table1").Offset(oSht.Range("Table1").Rows.Count + 1, 0).End(xlUp).Row ' ' last visible table row using End(xlDown) ' jLastVisibleTable = oSht.Range("Table1").End(xlDown).Row

So using End(xlUp) starting from the first row after the end of the table returns Row 25 even though that row is hidden.

But End(xlDown) works the same way with a table as with a Range, and so returns row row 20 which is indeed the last visible row in the table.

### Range.Find

My favourite method is to use Range.Find.

Using Find on Formulas includes hidden rows, whereas using Find on Values excludes hidden rows.

You can use this method on Worksheet.Cells or on a Range or Table.

' ' last row containing data (using Find in formulas) ' jLastRangeData = oSht.Range("NamedRange").Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' ' last visible row containing data (using Find in values) ' jLastVisibleRangeData = oSht.Range("NamedRange").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' ' last row containing data (using Find in formulas) ' jLastTableData = oSht.ListObjects("Table1").Range.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' ' last visible row containing data (using Find in values) ' jLastVisibleTableData = oSht.ListObjects("Table1").Range.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

- jLastRangeData returns 25
- jLastVisibleRangeData returns 20
- jLastTableData returns 25
- jLastVisibleTableData returns 20

### Methods using COUNT

Sometimes its simpler to just count the number of rows, add the starting row number and subtract 1.

‘

‘ last cell in Named Range

‘

jLastInRange = oSht.Range("NamedRange").Offset(oSht.Range("NamedRange").Rows.Count – 1, 0).Row

‘

‘ last row in named range current region

‘

jLastInRegion = oSht.Range("NamedRange").CurrentRegion.Rows.Count + oSht.Range("NamedRange").Row – 1

‘

‘ last row in Table

‘

jLastInTable = oSht.ListObjects("Table1").Range.Rows.Count + oSht.ListObjects("Table1").Range.Row – 1

‘

‘ last data row in table (excludes total row)

‘

jLastTableDataRow = oSht.ListObjects("Table1").ListRows.Count + oSht.ListObjects("Table1").Range.Row – 1

- jLastInRange returns 30 (it counts the empty cells too)
- jLastInRegion returns 25 (it excludes the bounding empty cells)
- jLastInTable returns 25
- jLastTableDataRow returns 24 (ListObject.ListRows excludes the total row and header row so I have not subtracted 1 for the header row)

## Conclusions

I was really surprised to find that End(xlUP) worked differently for Tables than for Ranges.

And with Tables sometimes it seems best to work with a Range that represents the table rather than directly with ListRows: the syntax is not always obvious to me.

There are many different ways of finding End rows but mostly I use range.find as the most fool-proof method (but there are still snags with things like Merged cells).

**What’s your most frequently used method?**

Welcome back!

Finding the extent of data ranges of variable size is something I need to do in almost all my VBA routines, and I’m not very consistent about it, so I’m definitely going to have a closer look at the methods presented here. I haven’t even used range.find for that purpose, so I’ll try that out first.

Thanks for the post.

Hi,

I often use the End(xlUp) method starting from the last cell (Cells(1048576, 1)) of the first colum of the sheet… although I wasn’t aware of it’s limits regarding hidden cells!

Thanks for this topic. I will consider using range.find now.

My favourite method is to use the CurrentRegion property of a range (assuming that I have contiguous range).

Range.Find is my goto – I do dislike having to search twice when trying to find the last cell, once for the row and once for the column

About the impact of formatting, I think it’s worth noting that formatting the whole column won’t have any impact on the used range.

Also, I tried both UsedRange and xlCellTypeLastCell methods and got the same results. I’m running the latest version of Office 365 and applied some shading on one cell for the test.

Yes: Excel is clever enough to know that mostly applying formats to whole columns does not have the ffect of formatting each individual cell.

LastCell gives the same result as UsedRange except when there are hidden/filtered cells.

If it is last cell of a specified column then

Sub Test()

Debug.Print [MAX(MATCH(mArray,A:A))]

End Sub

Where mArray is a name defined as

{“Ω”,9.9E+305}

This is a nice bit of research and presentation. I prefer to import a worksheet into a custom class and work out the row counts from the array bounds. Since I do a lot of manipulation of the data, the class is faster than VBA object references, and I can compare multiple sheets with a dictionary-like data structure.

Anyway, the heart of the custom class is a function called TrueRange, which I adapted from a snippet posted somewhere on the web. It uses the jLastRangeData method to ensure I capture hidden rows.

Cheers,

Mitch

Hi

I prefer to find the last row in a column, Clm. I chose the column number, Clm, which I know whose last data entry is a good indication of what I am personally interested in as the “last row”. For me personally this is almost always column 1. I try to get in the habit of usually arranging my data like that.

Then I do something of the form:

Lr = Ws1.Cells.Item(Ws1.Rows.Count, Clm).End(xlUp).Row

( Rather then starting from the last row in the worksheet, ( Ws1.Rows.Count ) , then I might sometimes start from somewhere else, that is to say a different row not as far down as the last in the worksheet )

I would use the Range.Find by rows if I was not sure which column had data in, something of the form:

Lr = Ws1.Cells.Find(What:=”*”, After:=Ws1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

I was usefully reminded here of this interesting alternative, which I had only previously seen only once before:

jLastUsed = oSht.UsedRange.Rows(oSht.UsedRange.Rows.Count).Row

I personally do not play around much hidden cells, or Tables, so issues related to those areas are not of interest to me currently, but should I do anything in that area in the future , then it will be very useful to look here again.

However the point about the ….”…. Find on Formulas includes hidden rows, whereas using Find on Values excludes hidden rows….”…. is very useful to know when searching in general with the Range.Find

I wouldn’t touch the SpecialCells(xlCellTypeLastCell) with a barge pole. It is messy because it works on the memory of the last UsedRange which may not be the actual current UsedRange. If you are going to use it then you need to get in the habit of doing something like this:

Ws1.UsedRange.SpecialCells(xlCellTypeLastCell).Row

This is because any call of the UsedRange will update the memory of the UsedRange which is then used by SpecialCells(xlCellTypeLastCell).

For example:

Sub BugMeWithABargePoll()

Dim Ws1 As Worksheet: Set Ws1 = Worksheets.Item(1)

Ws1.Cells.Clear ‘ We have no UsedRange after this

Dim Lr As Long

Let Ws1.Range(“A20”).Value = “Somefink” ‘ We have now a UsedRange

Let Lr = Ws1.Cells.SpecialCells(xlCellTypeLastCell).Row ‘ UsedRange is cell A20, Lr = 20

Ws1.Cells.Clear ‘ We have no UsedRange after this

Let Lr = Ws1.Cells.SpecialCells(xlCellTypeLastCell).Row ‘ No UsedRange, but still Lr = 20

Let Lr = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row ‘ No UsedRange, but still Lr = 20

Let Lr = Ws1.Range(“A1”).SpecialCells(xlCellTypeLastCell).Row ‘ No UsedRange, but still Lr = 20

Let Lr = Ws1.UsedRange.SpecialCells(xlCellTypeLastCell).Row ‘ No UsedRange, and now Lr = 1

‘ The call of UsedRange in the last line has updated Excel’s memory of the UsedRange

End Sub

Alan