Select Used Range and Format as Table

srw1983

New Member
Joined
Jul 14, 2009
Messages
10
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.

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

Try again using the sheet reference like this...
Rich (BB code):
ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes).Name = "Table1"

or this equivalent...
Rich (BB code):
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!
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,289
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top