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!
CodePudding user response:
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