VBA Set the last row to the First Row with data

[decadence]

Hi, Is it possible to set a range from the last row of data to the first row of data found in Columns AA, My data is varied and the row can change where my data starts from. There are no headers and there may be empty/blank cells above the first cell with data in.

 

[Joe4]

Try this:

Sub FindRange()

    Dim fRow As Long
    Dim lRow As Long
    Dim myRange As Range
    
'   Find first row with data
    If Cells(1, "AA") <> "" Then
        fRow = 1
    Else
        fRow = Cells(1, "AA").End(xlDown).Row
    End If
    
'   Find last row with data
    lRow = Cells(Rows.Count, "AA").End(xlUp).Row
    
'   Set range
    If lRow >= fRow Then
        Set myRange = Range(Cells(fRow, "AA"), Cells(lRow, "AA"))
        MsgBox "Range is " & myRange.Address
    Else
        MsgBox "No data in column AA"
    End If
        
End Sub

 

[decadence]

Thanks Joe4, This is what I was looking to happen, much appreciated

 

[Joe4]

You are welcome!