There are many occasions when we want to find the last row or the last column. This post is about the **Correct **way to find the last row or the last column.

Below mentioned are 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 stackoverflow for 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.

#### Find Last Row in a Sheet

To find the actual 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 **error.

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

Similarly we can find the last column as well.

#### Find Last Column in a Row

To find the last column in **Row 1** use this

With Sheets("Sheet1") lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With

#### Find Last Column in a Sheet

To find the actual last column in the sheet, use this.

With Sheets("Sheet1") If Application.WorksheetFunction.CountA(.Cells) = 0 Then lastCol = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else lastCol = 1 End If End With