How can I turn a Range ("A1:A11") into (A"1: until empty cell")

Question

I have tried to just set the range to ("A:A") but that makes the table too large and my computer freezes up, I have also tried to input a line like Range("A" & Rows.Count).End(xlUp).Offset(1) but that is not recognized by VBA. Any help would be appreciated!

Answer 1

You need to first define your last row by referencing the last cell in the column then use .End(xlUp).row to find the last row number. You can then use that row number to build cell references, or even save the range as a range variable like I did:

Sub Last_Row_Example()
    
    Dim LastRow As Long   'Last Row as a long integer
    Dim RG As Range       'A range we can reference again and again very easily
                          'Consider renaming it to something more descriptive.
                          'for your particular situation
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row ' Here we store the "LastRow" Number
    Set RG = Range("A1:A" & LastRow) ' Here we build a range using the LastRow variable.
        
    RG.Select
        Application.CutCopyMode = False
        ActiveSheet.ListObjects.Add(xlSrcRange, RG, , xlYes).Name = _
            "Table3"
        Range("Table3[[#All],[Ticker Name]]").Select
        Selection.ConvertToLinkedDataType ServiceID:=268435456, LanguageCulture:= _
            "en-US"
        
End Sub