NOTE: I intend to make this a "one stop post" where you can use the Correct
way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.
Unreliable ways of finding the last row
Some of the most common ways of finding last row which are highly unreliable and hence should never be used.
- UsedRange
- xlDown
- CountA
UsedRange
should NEVER be used to find the last cell which has data. It is highly unreliable. Try this experiment.
Type something in cell A5
. Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cell A10
red. If you now use the any of the below code, you will still get 5. If you use Usedrange.Rows.Count
what do you get? It won't be 5.
Here is a scenario to show how UsedRange
works.
xlDown
is equally unreliable.
Consider this code
lastrow = Range("A1").End(xlDown).Row
What would happen if there was only one cell (A1
) which had data? You will end up reaching the last row in the worksheet! It's like selecting cell A1
and then pressing End key and then pressing Down Arrow key. This will also give you unreliable results if there are blank cells in a range.
CountA
is also unreliable because it will give you incorrect result if there are blank cells in between.
And hence one should avoid the use of UsedRange
, xlDown
and CountA
to find the last cell.
Find Last Row in a Column
To find the last Row in Col E use this
With Sheets("Sheet1")
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With
If you notice that we have a .
before Rows.Count
. We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using .
before Rows.Count
and Columns.Count
. That question is a classic scenario where the code will fail because the Rows.Count
returns 65536
for Excel 2003 and earlier and 1048576
for Excel 2007 and later. Similarly Columns.Count
returns 256
and 16384
, respectively.
The above fact that Excel 2007+ has 1048576
rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Long
instead of Integer
else you will get an Overflow
error.
Note that this approach will skip any hidden rows. Looking back at my screenshot above for column A, if row 8 were hidden, this approach would return 5
instead of 8
.
Find Last Row in a Sheet
To find the Effective
last row in the sheet, use this. Notice the use of Application.WorksheetFunction.CountA(.Cells)
. This is required because if there are no cells with data in the worksheet then .Find
will give you Run Time Error 91: Object Variable or With block variable not set
With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastrow = 1
End If
End With
Find Last Row in a Table (ListObject)
The same principles apply, for example to get the last row in the third column of a table:
Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1") 'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")
With tbl.ListColumns(3).Range
lastrow = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
End Sub
There is one rather sneaky trick not mentioned here so far. We assume that you have no extra way to store data, but that is not strictly true.
One way around your problem is to do the following horrible thing, which should not be attempted by anyone under any circumstances: Use the network traffic to store data. And no, I don't mean NAS.
You can sort the numbers with only a few bytes of RAM in the following way:
- First take 2 variables:
COUNTER
and VALUE
.
- First set all registers to
0
;
- Every time you receive an integer
I
, increment COUNTER
and set VALUE
to max(VALUE, I)
;
- Then send an ICMP echo request packet with data set to
I
to the router. Erase I
and repeat.
- Every time you receive the returned ICMP packet, you simply extract the integer and send it back out again in another echo request. This produces a huge number of ICMP requests scuttling backward and forward containing the integers.
Once COUNTER
reaches 1000000
, you have all of the values stored in the incessant stream of ICMP requests, and VALUE
now contains the maximum integer. Pick some threshold T >> 1000000
. Set COUNTER
to zero. Every time you receive an ICMP packet, increment COUNTER
and send the contained integer I
back out in another echo request, unless I=VALUE
, in which case transmit it to the destination for the sorted integers. Once COUNTER=T
, decrement VALUE
by 1
, reset COUNTER
to zero and repeat. Once VALUE
reaches zero you should have transmitted all integers in order from largest to smallest to the destination, and have only used about 47 bits of RAM for the two persistent variables (and whatever small amount you need for the temporary values).
I know this is horrible, and I know there can be all sorts of practical issues, but I thought it might give some of you a laugh or at least horrify you.
Best Answer
This might help:
Get how many rows your sheet has and work with it. See below for more information. Beware: it may be glitchy if the sheet is empty.
Replace
ActiveWorkbook.Sheets("Sheet 8")
withActiveSheet
. Unfortunately, ActiveSheet does not provide autocomplete, but it is a Sheet object. Casting it to any Sheet object would give you that behavior.Well, once you want your code to deal with the same range twice, you might as well save it in a variable.
Considering your table may expand in rows and columns, you need to count them using the
End
command.Here's the updated code:
Take a look at my range. I use
sht.Range
because when you don't say where your range is, Excel assumes it is your selected sheet. If it isn't you must explicitly say so. In your case, it doesn't matter.