+ Reply to Thread
Results 1 to 15 of 15

Unable to Delete Table Rows

  1. #1
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Unable to Delete Table Rows

    I am trying to add a Delete option on a userform. The record to delete is selected from a listbox and the userform is populated with the appropriate data.
    I have looked at the code step-by-step and it appears that the error occurs at the findvalue.EntireRow.Delete line. I temporarily added a textbox to the form to insure that the value for "findvalue" was correct and it was indicating that the proper record was being located in the underlying table however I get a runtime error. Any help or suggestions would be appreciated.


    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 12-15-2019 at 10:18 AM. Reason: Fixed code tags!
    Proud to be Canadian!

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,050

    Re: Unable to Delete Table Rows

    If the sheet is protected, the macro must unprotect it before it can delete the row.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Re: Unable to Delete Table Rows

    Thank you for your quick reply. The sheet is not protected at this point but thank you for the reminder because it will be eventually. Any other suggestions?

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,050

    Re: Unable to Delete Table Rows

    Could you attach a copy of your file, de-sensitized if necessary?

  5. #5
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Re: Unable to Delete Table Rows

    This is just a development copy, so all data is bogus anyway.
    https://drive.google.com/file/d/1k-E...ew?usp=sharing

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,050

    Re: Unable to Delete Table Rows

    I suspect that the error is caused by the fact that when trying to delete the entire row, parts of that row are outside of a few tables and for some reason, the macro doesn't like that. I used the macro below to convert all the tables in the "Inventory" sheet to ranges and then it seems to work properly.
    Please Login or Register  to view this content.
    Last edited by Mumps1; 12-15-2019 at 12:03 PM.

  7. #7
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Re: Unable to Delete Table Rows

    Thank you. I'll go through and convert all tables to dynamic ranges and see if that helps..

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,789

    Re: Unable to Delete Table Rows

    You can keep the tables & use
    Please Login or Register  to view this content.
    If you delete the entire row, it will affect other data on the sheet.

  9. #9
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Unable to Delete Table Rows

    Keep the table, just try replacing this:
    Please Login or Register  to view this content.
    with:
    Please Login or Register  to view this content.

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,789

    Re: Unable to Delete Table Rows

    @Akuini
    That will only delete the cell, not the table row.

  11. #11
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Unable to Delete Table Rows

    Quote Originally Posted by Fluff13 View Post
    @Akuini
    That will only delete the cell, not the table row.
    Are you sure?
    Actually, I tried it again, it did delete the table row. But I didn't use the OP's file, I just tried it like this (it is a real table):

    Excel 2013 32 bit
    B
    C
    D
    1
    Name1 Name2 Name3
    2
    Truman Aaden Ruben
    3
    Landyn Abraham Zachariah
    4
    Jeremiah Nehemiah Owen
    5
    Rolando Anson Connor
    6
    Ford Leandro Clayton
    Sheet: Sheet1

    Run this:

    Please Login or Register  to view this content.
    The result:
    Excel 2013 32 bit
    B
    C
    D
    1
    Name1 Name2 Name3
    2
    Truman Aaden Ruben
    3
    Landyn Abraham Zachariah
    4
    Rolando Anson Connor
    5
    Ford Leandro Clayton
    Sheet: Sheet1

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,789

    Re: Unable to Delete Table Rows

    Apologies for that, your quite right it does work.
    I didn't realise that tables worked that way.

  13. #13
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Unable to Delete Table Rows

    No need to apologize, thanks for the feedback.

  14. #14
    Forum Contributor
    Join Date
    04-07-2015
    Location
    Halifax, NS
    MS-Off Ver
    365
    Posts
    100

    Re: Unable to Delete Table Rows

    Thanks everyone for your help. Hope you all had a very happy holiday.

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,789

    Re: Unable to Delete Table Rows

    You're welcome & thanks for the feedback

+ 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. [SOLVED] Macro to Autofilter a table and delete filtered rows: Error 1004 cannot alter table
    By brytsyt in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-05-2018, 04:57 AM
  2. [SOLVED] Delete table rows in multiple table is specific range
    By 3345james in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2017, 08:32 AM
  3. Unable to delete certain rows
    By Wiking in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2016, 09:37 AM
  4. Unable to insert or delete rows
    By AvrilA in forum Excel General
    Replies: 3
    Last Post: 04-12-2016, 02:53 AM
  5. Unable to delete rows
    By Kingsta in forum Excel General
    Replies: 1
    Last Post: 06-01-2014, 05:49 AM
  6. [SOLVED] Unable to delete rows
    By Mrowe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2012, 03:08 PM
  7. [SOLVED] Unable to delete rows or columns? Why?
    By Barney Quinn in forum Excel General
    Replies: 3
    Last Post: 09-17-2006, 09:43 PM

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