Ms Excel VBA Select All or (Ctrl + A) Code

Question

This is my sample data.

And this is the VBA code produced with macro when I selecting all (ctrl + A) inside A1:C3, then change the table format.

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

Output

The problem started when I have more data, let say bigger range A1:C4.

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

For the equivalent of Ctrl + A go down to the CurrentRegion section

UsedRange - All cells (with values)

The simplest change you can do to your code to include everything is changing Range("$A$1:$C$3") to ActiveSheet.UsedRange.
Note that this will make a table to include everything in the sheet, so if you have other data in there, you need a different approach. If you want everything included, then great, you can trim some of the code away if you like:

Sub Macro1()
    ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes).Name = _
        "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium9"
End Sub

CurrentRegion - All connected cells (with values)

If you want a more constrained way of doing it, you can use CurrentRegion instead.
If you know that the table will always be including for example A1 then you could use Range("A1").CurrentRegion instead:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = _
    "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium9"

If location is not a given, you could even select a cell before running it, and use Selection.CurrentRegion instead.

Example run with the following code:

Sub Macro1()
    Set objListObject = ActiveSheet.ListObjects.Add(xlSrcRange, Selection.CurrentRegion, TableStyle = "TableStyleMedium9")
End Sub

Answer 2

In the following code, you only select and assign only the data in the A1:C3 range.

Range("A1:C3").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$3"), , xlYes).Name = _
"Table1"

There's no way to change the code at any time based on your dynamic range, this time you need to include the A4:C4 range, but next time you might include A5:C5, the code won't adapt.

What you need to do is change it manually every time you change the range, change the parameters in the above two paragraphs, for example, you need to include A4:C4, then the corresponding two paragraphs of A1:C3 need to be changed to A1:C4：

Range("A1:C4").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$4"), , xlYes).Name = _
"Table1"