+ Reply to Thread
Results 1 to 6 of 6

VBA Required to resize Excel table so that first 2 table columns have table format removed

  1. #1
    Registered User
    Join Date
    11-14-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    VBA Required to resize Excel table so that first 2 table columns have table format removed

    Hi,

    In Excel I can click on "Table Design", "Resize Table" and amend "=$B$23:$O$72" to "=$D$23:$O$72".

    I've searched but can't find VBA code for this (note that columns are fixed but number of rows in the table is dynamic and will change each time therefore any code would need to account for that).

    I want to remove columns B & C from the table range in order that some non-table formatting can be applied to columns B & C (this is for a Public Sub that first populates the table).

    Thanks in advance for any help you can give on this.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA Required to resize Excel table so that first 2 table columns have table format rem

    Something like below. Change table reference, ListObjects(), as needed.

    Please Login or Register  to view this content.
    Note: Each time the code is run, it will remove first 2 columns from table. You can add additional checks as needed.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    11-14-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: VBA Required to resize Excel table so that first 2 table columns have table format rem

    Thanks so much CK76 - I've implemented this code and works great.

    Now I have it in place though I realize I'm going to need to start my sub with a check to see if the table format is applied to columns B & C and if not then set table range to "=$B$23:$O$72". This will allow me to make changes to the data that is pulled into the table and then re-run my sub each time. Would appreciate any help with code for this initial check and table range set which will help me finish of this piece and have an amazing little macro in place!

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA Required to resize Excel table so that first 2 table columns have table format rem

    Rather than set range to B23:O72. Why not only run code when B:C columns are already in table range?

    Something like...
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-14-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: VBA Required to resize Excel table so that first 2 table columns have table format rem

    thanks again ck76 much appreciated, I probably need to add a little more detail here - my bad.

    Before the format is removed from columns B & C by the macro (per the first solution you provided) I have some other code which is pulling data from another tab into the table.

    In order for users to be able to rerun the macro (so they can tinker with the source data if not happy with results after the macro runs each time) I need the macro to be able to restore the table to the original column range (so that B & C are included) end row number is variable so actually preferable to not include (or at least not to have to specify).

    The first time the macro is run the table is set to include columns B&C so I was thinking of an if statement along the lines of: If table range does not include B&C then amend range to include B&C otherwise end if

    hope that makes some kind of sense!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VBA Required to resize Excel table so that first 2 table columns have table format rem

    In that case I'd probably add argument in the sub.

    Please Login or Register  to view this content.
    Now, I don't know how your other procedure is set up...

    So there are 2 ways to use this argument.

    1. Use If statement to check for column count.
    Ex:
    Please Login or Register  to view this content.
    2. Depending on where in your code, you call this procedure... Add True or leave it out.

    Note: I didn't add Table as argument, but if you do that, you can make it more flexible.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy table from web page into Excel in table format
    By terry87 in forum Excel General
    Replies: 6
    Last Post: 01-01-2018, 10:35 PM
  2. [SOLVED] Entries on pivot table removed when table cell = 0
    By lockers in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-23-2014, 12:29 AM
  3. [SOLVED] Resize table to same row count as a piovt table
    By djon5020 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2014, 01:43 AM
  4. Add row to end of table and resize table automatically
    By cdthimann in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2013, 03:30 AM
  5. [SOLVED] Is it possible to resize a table and keep format & formulas?
    By NathanC in forum Excel General
    Replies: 1
    Last Post: 11-03-2012, 12:04 AM
  6. Replies: 0
    Last Post: 09-10-2012, 09:19 AM
  7. Excel 2007 Pivot Table help required to total columns repeated month
    By wendy19804 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2011, 11:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1