Select Used Range and Format as Table

[srw1983]

Greetings folks!

I just recorded this Macro in my spreadsheet:

'Format as Excel Table
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$Q$230"), , xlYes).Name = _ "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium16"

The problem is, that the rows in my table increase day by day. I tried to put in UsedRange, but that just got me an error message.

Maybe there is another way to format the UsedRange as a Table?

Thank you for your responce in advance and best regards,
Stefan

 

[Jerry Sullivan]

Greetings Stephan,

I tried to put in UsedRange, but that just got me an error message.

You might have gotten an error if you tried to reference the UsedRange like this because you need to include a reference to the sheet.

'This returns an error:
ActiveSheet.ListObjects.Add(xlSrcRange, UsedRange, , xlYes).Name = "Table1"

Try again using the sheet reference like this...

ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes).Name = "Table1"

or this equivalent...

With ActiveSheet
    .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "Table1"
End With

Be aware that a Sheet's UsedRange might be larger than its Data Range. You can find plenty of examples on this site of how to reference the Data Range if that is what you want to do.

Good luck!