Macro to Format Table

Question

Hi,

I've recorded a macro and the result as as below:

    Range("A1").Select

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AO$472"), , xlNo).Name = _

        "Table1"

    Range("Table1[#All]").Select

    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight13"

But instead of fixing the Range as ("$A$1:$AO$472"), how can I make it to select different range?

Thanks.

Answer 1

If row 1 already contains column headers (field names), change xlNo to xlYes.

Answer 2

If you want to turn the current selection into a range, change

    Range("A1").Select

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AO$472"), , xlNo).Name = _

"Table1"

to

ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlNo).Name = _

"Table1"

If you want to turn the contiguous range containing A1 into a table, use

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlNo).Name = _

"Table1"

Answer 3

The second one work but not sure why it added another row on top as a header? This is the code I used

Range("A1").Select

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlNo).Name = _

    "Table1"

    Range("Table1[#All]").Select

    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight13"