Excel - Find the last row or column in the Data Range

Sometime we wrote VBA code to find the last cells in a data range.

If there is no blank in the middle of the data range, the following code can be used.

    Sub LastCellInRange()

    RowNo = Range("A1").End(xlDown).Row

    ColumnNo = Range("A1").End(xlToRight).Column

    End Sub

However, if there is the data range contains only one data, the code would give us the wrong information.

For example,

The result from running the above code is

RowNo=1048576

ColumnNo=16384

By modifying the above code, we can get the right answer.

Sub LastCellInRange()

Dim i, j, RowNo, ColumnNo As Integer

'i,j decide the location of the first cell in the range

i = 1

j = 1

If Cells(i + 1, j) <> "" Then RowNo = Cells(i, j).End(xlDown).Row Else RowNo = i

If Cells(i, j + 1) <> "" Then ColumnNo = Cells(i, j).End(xlToRight).Column Else ColumnNo = j

 

End Sub

The result from running the above code is

RowNo=1

ColumnNo=1

© Copyright Exceltipsandkeys All Rights Reserved.