Find Last Row or Column

Find the last Row or Column

One basic need in working with VB projects is finding the last used row or column on a worksheet. Following are a few methods to get what you need.

The first method is not a very exact method to use because it depends on Excel to track the last cell and it doesn’t always work as expected.
[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Find Last Row 1″]
Option Explicit
Sub lastrow1()
Dim lastRow As Long
lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
MsgBox "The last used row is " _
& lastRow
End Sub
[/vb]

If you know that the file-type that the macro will be run on will always be .xls, you can use the following to go to the last cell of a particular column (in this case column A). Of course if it is an Excel 07 or 10 file (.xlsx, or .xlsm) you could change the Range("A65536") value to Range("A1048576")
[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Find Last Row 2″]
Option Explicit
Sub lastrow2()
Dim lastRow As Long
lastRow = Range("A65536").End(xlUp).Row
MsgBox "The last Row used in Column A is " _
& lastRow
End Sub
[/vb]

Here is a better method using the UsedRange function.
Range("A65536") value to Range("A1048576")
[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Find Last Row 3″]
Option Explicit
Sub lastrow3()
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count
MsgBox "The last Row used in the worksheet is " _
& lastRow
End Sub
[/vb]

A more reliable method can use the SearchOrder functionality.
Range("A65536") value to Range("A1048576")
[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Find Last Row 4″]
Sub lastrow4()
Dim lastRow As Long
lastRow = Cells.Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox "The last Row used in Column A is " _
& lastRow
End Sub
[/vb]

The above methods can be adjusted to find the last columns as well, here are some macros to find the last column used.
Range("A65536") value to Range("A1048576")
[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Find Last Column 1″]
Option Explicit
Sub lastColumn1()
Dim rLastCell As Range

Set rLastCell = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

MsgBox ("The last used column is: " & rLastCell.Column)
End Sub
[/vb]

This methods can be used on any sheet, not just the active sheet.