Finding The Last Cell, Last Row, or Last Column

Find last row, column or last cell

Thanks to Ron de Bruin

Copy the code in a Standard module of your workbook, if you just started with VBA see this page. Where do I paste the code that I find on the internet

 

Last used cell in one row or one column

The macro’s give you the row or column number of the last cell with data in one row or one column. Note: This code below will not work correct if the last row with data is hidden.

Sub LastRowInOneColumn()
‘Find the last used row in a Column: column A in this example
Dim LastRow As Long
With ActiveSheet
LastRow = Cells(Rows.Count, “A”).End(xlUp).Row
End With
MsgBox LastRow
End Sub

 

Sub LastColumnInOneRow()
‘Find the last used column in a Row: row 1 in this example
Dim LastCol As Integer
With ActiveSheet
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
End With
MsgBox LastCol
End Sub

 

Last used cell in a worksheet

Possible problems with xlCellTypeLastCell and UsedRange are: The last cell will only re-set when you save (or save/close/reopen the file). If cell formatting is changed it will not reset the last cell, clearing the data is not enough, you must delete the rows or columns then, See: http://www.contextures.com/xlfaqApp.html#Unused

So when using VBA you cannot rely on this macros if you want the last row or column with data on your worksheet. Last Row :

Sub xlCellTypeLastCell_Example_Row()
Dim LastRow As Long
With ActiveSheet
LastRow = Range(“A1”).SpecialCells(xlCellTypeLastCell).Row
End With
MsgBox LastRow
End Sub

Sub UsedRange_Example_Row()
Dim LastRow As Long
With ActiveSheet.UsedRange
LastRow = Rows(Rows.Count).Row
End With
MsgBox LastRow
End Sub

Last Column :

Sub xlCellTypeLastCell_Example_Column()
Dim LastColumn As Long
With ActiveSheet
LastColumn = Range(“A1”).SpecialCells(xlCellTypeLastCell).Column
End With
MsgBox LastColumn
End Sub

 

Sub UsedRange_Example_Column()
With ActiveSheet.UsedRange
LastColumn = Columns(Columns.Count).Column
End With
MsgBox LastColumn
End Sub

 

Use the “Last” function to Find the last row, column or cell in range or worksheet

In the example macros we call the function Last, this function have two arguments Argument 1 can be 1, 2 or 3 1 = last row 2 = last column 3 = last cell

Argument 2 is the range where you want to search in Copy the example macros together with the function “Last” in a normal module of your workbook. Note: There is no test if you exceed the amount of rows and columns in the examples below, add that yourself.

Sub LastRow_Example()

Dim LastRow As Long

Dim rng As Range

 

‘ Use all cells on the sheet

Set rng = Sheets(“Sheet1”).Cells

 

‘Use a range on the sheet

‘Set rng = Sheets(“Sheet1”).Range(“A1:D30”)

 

‘ Find the last row

LastRow = Last(1, rng)

 

‘ After the last row with data change the value of the cell in Column A

rng.Parent.Cells(LastRow + 1, 1).Value = “Hi there”

 

End Sub

 

 

Sub LastColumn_Example()

Dim LastCol As Long

Dim rng As Range

 

‘ Use all cells on the sheet

Set rng = Sheets(“Sheet1”).Cells

 

‘Or use a range on the sheet

‘Set rng = Sheets(“Sheet1”).Range(“A1:D30”)

 

‘ Find the last column

LastCol = Last(2, rng)

 

‘ After the last column with data change the value of the cell in row 1

rng.Parent.Cells(1, LastCol + 1).Value = “Hi there”

 

End Sub

 

 

Sub LastCell_Example()

Dim LastCell As String

Dim rng As Range

 

‘ Use all cells on the sheet

Set rng = Sheets(“Sheet1”).Cells

 

‘Or use a range on the sheet

‘Set rng = Sheets(“Sheet1”).Range(“A1:D30”)

 

‘ Find the last cell

LastCell = Last(3, rng)

 

‘ Select from A1 till the last cell in Rng

With rng.Parent

.Select

.Range(“A1”, LastCell).Select

End With

End Sub

This is the “Last” function that we use in the macro’s above :

Function Last(choice As Long, rng As Range)

‘ 1 = last row

‘ 2 = last column

‘ 3 = last cell

Dim lrw As Long

Dim lcol As Long

 

Select Case choice

 

Case 1:

On Error Resume Next

Last = rng.Find(What:=”*”, _

After:=rng.Cells(1), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Row

On Error GoTo 0

 

Case 2:

On Error Resume Next

Last = rng.Find(What:=”*”, _

After:=rng.Cells(1), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Column

On Error GoTo 0

 

Case 3:

On Error Resume Next

lrw = rng.Find(What:=”*”, _

After:=rng.Cells(1), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Row

On Error GoTo 0

 

On Error Resume Next

lcol = rng.Find(What:=”*”, _

After:=rng.Cells(1), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Column

On Error GoTo 0

 

On Error Resume Next

Last = rng.Parent.Cells(lrw, lcol).Address(False, False)

If Err.Number > 0 Then

Last = rng.Cells(1).Address(False, False)

Err.Clear

End If

On Error GoTo 0

 

End Select

End Function

If you only want to get the last Row or Column number of a worksheet you can also use the two seperate functions below if you want, but in the function above you have all  options in one and also can check a range instead of the whole worksheet. you use the functions below like this :

Lr = LastRow(ActiveSheet) Lc = LastCol(ActiveSheet)

Function LastRow(sh As Worksheet)

On Error Resume Next

LastRow = sh.Cells.Find(What:=”*”, _

After:=sh.Range(“A1″), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Row

On Error GoTo 0

End Function

 

 

Function LastCol(sh As Worksheet)

On Error Resume Next

LastCol = sh.Cells.Find(What:=”*”, _

After:=sh.Range(“A1”), _

Lookat:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Column

On Error GoTo 0

End Function

 

Caveats:

1 : Tom Ogilvy posted this in the newsgroup. Find does not find a cell containing the Null string “” entered when you do Edit=>Copy and then Edit=>Paste Special, Values for a cell containing a formula like =IF(A1=””,””,A1*1.21), which may or may not be desirable (end(xlup) does). 2 : Find not see cell formatting but only data, if this is important for you see the xlCellTypeLastCell and UsedRange section of this page to find the last row or column. 3 : If you use merged cells (Please do not use merged cells) maybe you get unexpected results. It will give you the column number of the first cell and not the last cell in the merged cells.

thanks to Ron De Bruin