Microsoft Office Forums - View Single Post - NEWB to Macros

charlesdh · 06-18-2014, 10:13 AM

Hi,

Here is 2 more methods to get the "Last" row.
(Not sure if Bob mentioned this)
The first one uses a excel function for "Special Cells
The second will look from the bottom of the column to the first row with data.

Hope these help you..

Code:

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

Code:

Sub Last_Row()
''' This is different from other example
''' We'll use the XlUp format'''
''' This looks from the bottom up ''
Dim LastRow As Long
    With ActiveSheet
        LastRow = .Range("A65536").End(xlUp).Row
    End With
    MsgBox LastRow
End Sub

I use the second code often. If you want to know the "Last" unused row in a column you can add +1 after the row.
You can use this if you need to add data to the column.

Code:

 LastUnusedRow = .Range("A65536").End(xlUp).Row+1