Search code examples
excellistobject

VBA: Create a table from the suggested range

Every day I get a excel sheet with some data in a cell range. I recorded a macro which turns a defined range into a proper table and then processes the data:

Sub Macro1
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$CP$8221"), , xlYes).Name _
        = "myTable"
    ' Further processing follows
End Sub

My problem is that the Range varies. It has a different number of lines and columns every day. What command should I use instead to define a table of the range suggested by default? By default I mean the range you get as a suggestion when you place the cursor anywhere inside the data range and hit Ctrl+T to create a table.

Solution

  • Scott Craner wrote a sufficient answer in his comment: Range("A1").CurrentRegion