+ Reply to Thread
Results 1 to 8 of 8

Delete a Table

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    286

    Delete a Table

    Hello,

    I have the below code to add a table of data to a calculation file. The table add copies the first existing table and then finds the next available column next to the table and pastes then giving you the option to name the new table. The file is locked down so once the table is added it cannot be deleted unless I unlock the file and delete. There are too many files out there for this. I was wondering if anyone had a suggestion on code to set up a delete table button. Click the button and a window pops asking which table you want to delete. It finds the table and deletes it. The table is three columns wide. The table name is in the third column at the top of the table so when it finds the table name it would need to delete the column it finds plus 2 columns to the left. Any help would be greatly appreciated. The file is too large to load to the site. The table name is in row 17. People can continue to add tables so it has to look at the entire row 17 and not a range.

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: Delete a Table

    my assumptions:
    *there are no blank rows in the middle of the table you want to delete, then try this:
    *the table is only in one sheet (same sheet where you'll put the delete button)
    *all columns of the table has a header tittle
    *there are no other table sitting right beside the table you want to delete


    Please Login or Register  to view this content.
    that's a recorded macro..
    just replace the "First Header of the table", but retain the quotation marks,
    thus if you want to find the word cake
    do it like:
    Please Login or Register  to view this content.
    don't worry, there's a lot of people that are far more confused than you
    but if you liked what i suggested. Click for me the "Add Reputation" - that way, we'd be both happy.

  3. #3
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: Delete a Table

    my assumptions:
    *there are no blank rows in the middle of the table you want to delete, then try this:
    *the table is only in one sheet (same sheet where you'll put the delete button)
    *all columns of the table has a header tittle
    *there are no other table sitting right beside the table you want to delete


    Please Login or Register  to view this content.
    that's a recorded macro..
    just replace the "First Header of the table", but retain the quotation marks,
    thus if you want to find the word cake
    do it like:
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    286

    Re: Delete a Table

    There are blank rows in the table. I have a end range using a named range on the cell at the bottom of the table. Also I want the person to be able to type in the table they want deleted and delete the columns. clear contents would just clear it and leave the columns there correct?

  5. #5
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: Delete a Table

    sorry about the double post above.. that occurred when my IE erred while posting the reply...

    yes, the ".clearcontents" statement would only remove the contents of the cell - not delete the cell and shift them somewhere

    thus:

    Please Login or Register  to view this content.
    in the code above, it will pop up an inputbox, so the user can type "the first header name"
    now, that word they will type has to be unique on the entire sheet, if not, it might find a duplicate word and delete it...

  6. #6
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    286

    Re: Delete a Table

    Thank you. I tried that code and it just deleted the table name and the rate in the next cell below the table name.

    Here is a sample of the tab that has the tables. The header label sits in one cell of the three column table so it needs to find the column that the name of the table is in and then select that column plus the two columns to the left and delete the entire three columns. Rate 101 would never be deleted which also has the macro to add adjustments to the tables below. So say I wanted to delete Rate 107. The message would come up. I punch in 107. It finds the column with 107 and selects that plus the two to the left, deletes and shifts remaining contents left.

    Any help would be great. Thanks!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-27-2012
    Location
    cebu, Philippines
    MS-Off Ver
    Excel 2016
    Posts
    210

    Re: Delete a Table

    I have no idea what your password for the sheet is.. so I created this:

    Please Login or Register  to view this content.
    table1.xlsm

    My assumptions are:
    1. the cells containing the table headers are only found in row 7... which means, you're not deleting or inserting rows in between rows 1 to 7
    2. the entire 3 columns containing the table header are all to be deleted... which means, there are no valuable data on cells above and below the selected table that needs to be retained.

    **if there are two tables with the same Number, then it can delete both of them given then they are not sitting beside each other, but if they are sitting to each other, it will skip the next one..

    given that I don't know what your password to unlock the sheet, you'll need to edit the part that says "TypeHere"
    you put your password in between the quotation marks

    so the code above will only stop searching if there are 6 successive cells in row 7 that contains nothing.


    If this helped.. click my star "*" below that says add reputation
    and

  8. #8
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2013
    Posts
    286

    Re: Delete a Table

    That worked great. I was even able to use the same code with a slight modification to get it to work on another tab. Thank you. I tried to add reputation but not sure it went through. maybe it is a chrome thing?

+ 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. delete raw+pivot table
    By Nicolas6 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-09-2012, 05:07 AM
  2. How do I delete a {=Table(A1)} function
    By Richard Buttrey in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 04-23-2010, 02:56 AM
  3. Delete Pivot table
    By hurriance in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2006, 04:10 AM
  4. Delete pivot table
    By HSalim[MVP] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-23-2005, 03:20 PM
  5. delete pivot table
    By billq in forum Excel General
    Replies: 3
    Last Post: 01-17-2005, 07:06 PM

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