Results 1 to 2 of 2

Select Used Range and Format as Table

This is a discussion on Select Used Range and Format as Table within the Excel Questions forums, part of the Question Forums category; 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 = _ ...

  1. #1
    New Member srw1983's Avatar
    Join Date
    Jul 2009
    Location
    Stuttgart, Germany
    Posts
    10

    Default Select Used Range and Format as Table

    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

  2. #2
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,259

    Default Re: Select Used Range and Format as Table

    Greetings Stephan,

    Quote Originally Posted by srw1983 View Post
    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.

    Code:
    'This returns an error:
    ActiveSheet.ListObjects.Add(xlSrcRange, UsedRange, , xlYes).Name = "Table1"
    Try again using the sheet reference like this...
    Code:
    ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes).Name = "Table1"
    or this equivalent...
    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!
    Using Excel 2010, 2013

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com