Reference the Single-Column Range From a Given Cell to the Last Non-Empty Cell in the Column Using the Range.End Property

• There are many ways to do it but the simplest, usually learned first, is to use the Range.End property.

• Range.End Property (Docs)

Hard-Coded (Basic)

Sub TestEndHardCoded()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range:
    Set rg = ws.Range("AA6", ws.Cells(ws.Rows.Count, "AA").End(xlUp))
    
    MsgBox "The referenced range is """ & rg.Address(0, 0) _
        & """ on worksheet """ & ws.Name & """.", vbInformation
        
End Sub

Dynamic (Simple)

Sub TestEndDynamic()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim fcell As Range: Set fcell = ws.Range("AA6")
    
    Dim rg As Range: Set rg = ws.Range(fcell, _
        ws.Cells(ws.Rows.Count, fcell.Column).End(xlUp))
    
    MsgBox "The referenced range is """ & rg.Address(0, 0) _
        & """ on worksheet """ & ws.Name & """.", vbInformation
        
End Sub

Dynamic (Using a Helper Function)

Sub TestEndFunction()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim fcell As Range: Set fcell = ws.Range("AA6")
    
    Dim rg As Range: Set rg = GetSingleColumnRangeEnd(fcell, True)
    
    If rg Is Nothing Then Exit Sub
    
    MsgBox "The referenced range is """ & rg.Address(0, 0) _
        & """ on worksheet """ & ws.Name & """.", vbInformation
        
End Sub

Function GetSingleColumnRangeEnd( _
    ByVal firstCell As Range, _
    Optional ByVal ShowMessage As Boolean = False) _
As Range
    Const PROC_TITLE As String = _
        "Reference Single-Column Range Using the End Property"
    
    With firstCell.Cells(1)
        
        Dim RowsCount As Long: RowsCount = .Worksheet.Cells( _
           .Worksheet.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        
        If RowsCount < 1 Then
            If ShowMessage Then
                MsgBox "No data found in range """ _
                    & .Resize(.Worksheet.Rows.Count - .Row + 1).Address(0, 0) _
                    & """ on worksheet """ & .Worksheet.Name & """!", _
                    vbExclamation, PROC_TITLE
                Exit Function
            End If
        End If
        
        Set GetSingleColumnRangeEnd = .Resize(RowsCount)
    
    End With

End Function

This method returns the range in column “I” as a Range object.

Sub used()

Set col = Columns("I")   'or
'Set col = ActiveCell.Column 'dynamical
first = col.Cells(1).End(xlDown).Address
last = col.Cells(rows.Count).End(xlUp).Address
Set usedrng = Range(first & ":" & last)

End Sub

In order to select only blanks, you might use this:

Selection.SpecialCells(xlCellTypeBlanks).Select

Maybe you can turn this into a way to remove the blanks from your selection in some way?

Good luck