How to Select All or (Ctrl + A) dynamic table doesn't matter how big it is

Question

This is my sample data.

And this is the VBA code produced with macro.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:C3").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$3"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium9"
End Sub

How do I get the macro code?

1. Developer > Record Macro
2. Select all (ctrl + A) inside any cells within A1:C3
3. Home > Format as Table

Output

The problem is my data is dynamic and not necessarily stay at A1:C3.

It could be bigger or smaller.

E.g., let say I've bigger within range A1:C4 in different Ms Excel file.

The code above won't select all, instead it selects only A1:C3.

Last row A4:C4 won't be affected with this code.

How do I change this doesn't matter how big the table is?

Answer 1

Solution 1

You can use Range("A1").CurrentRegion to get the area of continous data (which is the same as Ctrl + A):

Option Explicit

Public Sub SelectCurrentRegion()
    Dim MyData As Range
    Set MyData = Worksheets("Sheet1").Range("A1").CurrentRegion
    
    'don't use .select this is just for illustrating
    MyData.Select
End Sub

Note that the number in cell D6 is not vertically nor horizontally connected with the other data. Therefore it is not selected by Ctrl + A or .CurrentRegion.

Solution 2

Or Worksheets("Sheet1").UsedRange to get the area of all data:

Option Explicit

Public Sub SelectCurrentRegion()
    Dim MyData As Range
    Set MyData = Worksheets("Sheet1").UsedRange
    
    'don't use .select this is just for illustrating
    MyData.Select
End Sub

You might benefit from reading How to avoid using Select in Excel VBA.

Answer 2

@Pᴇʜ has already given you 2 solutions. Here is Solution 3. I would prefer finding last row and column over UsedRange and then construct the range. I have explained here why I do not prefer using UsedRange

Solution 3

Find the last row and last column and then create your range

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim rng As Range
    
    '~~> Change this to relevant sheet
    Set ws = Sheet1
    
    With ws
        LastRow = .Cells.Find(What:="*", _
        After:=.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row

        LastColumn = .Cells.Find(What:="*", _
        After:=.Range("A1"), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
        
        Set rng = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
        
        Debug.Print rng.Address
    End With
End Sub

Answer 3

If you are using Excel tables then you can use DataBodyRange or Range Properties to select what you need to select.

Dim lstObj As ListObject
For Each lstObj In ActiveSheet.ListObjects
    lstObj.DataBodyRange.Select ' Will select only data without headers
    lstObj.Range.Select ' Will select complete table
Next lstObj