Excel - Find the last row or column used in the sheet

Sometime we wrote VBA code to find the last used cells in the sheet.

Sub LastCellInSheet()

Dim i, j, RowNo, ColumnNo As Integer

'i, j decide the column or row you are interested in

i = 1

j = 1

RowNo = Cells(Rows.Count, j).End(xlUp).Row

ColumnNo = Cells(i, Columns.Count).End(xlToLeft).Column

  

End Sub

For example,

The result from running the above code is

RowNo = 10

ColumnNo = 3

However, if there is the data in the last column (Column XFD for excel 2007) or in the last row (Row 1048576 for excel 2007) , the code would give us the wrong information. The code would not count the last column and lost row.

It would not be a big issue because there is no record in the last column or last row in most of the cases. However, If you want to make sure the code is always working, we can modify the above code.

Dim i, j, RowNo, ColumnNo As Integer

'i, j decide the column or row you are interested in

i = 1

j = 1

If Cells(Rows.Count, j) <> "" Then RowNo = Rows.Count Else RowNo = Cells(Rows.Count, j).End(xlUp).Row

If Cells(i, Columns.Count) <> "" Then ColumnNo = Columns.Count Else ColumnNo = Cells(i, Columns.Count).End(xlToLeft).Column

© Copyright Exceltipsandkeys All Rights Reserved.