+ Reply to Thread
Results 1 to 9 of 9

Need better way to delete rows in a database.

  1. #1
    Forum Contributor
    Join Date
    09-12-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    127

    Need better way to delete rows in a database.

    Can someone enlighten me on whay this code only works about half of the time? I'm looking to delete the entire row in a database where the string "Delete" apprears in column A. In the database I am testing it on there is a block of 5 rows that meet this criteria. The code only deletes 3 of the 5 rows.
    thanks in advance, s4

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Need better way to delete rows in a database.

    Your code should work from what I see. However, maybe try a filter instead.

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Need better way to delete rows in a database.

    Yes.. it's an oldie but a classic.
    When you delete a row the whole table moves up one while the loop steps down one.... That means one row gets overlooked.

    What you need to do is start from the bottom of the table and move up, deleting rows as you go
    Elegant Simplicity............. Not Always

  4. #4
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Need better way to delete rows in a database.

    The code from Post# does not work if you delete a row and the next should also be deleted, but it will be skipped.
    there are several other options like, loop with a row counter and every time you delete reduce the counter or delete starting at the end, or use the autofilter like in Post #2 or add all the cells a to a range and delete the range at the end.

    Regards

  5. #5
    Forum Contributor
    Join Date
    09-12-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    127

    Re: Need better way to delete rows in a database.

    ah yes, makes sense. How would I instruct it start at the bottom and go up?

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Need better way to delete rows in a database.

    Please Login or Register  to view this content.
    You need to amend the red bits to suit
    Last edited by AndyLitch; 05-09-2013 at 11:19 AM.

  7. #7
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Need better way to delete rows in a database.

    JOHN H. DAVIS,

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    Adjust the next line of code to point to the correct worksheet:

    Please Login or Register  to view this content.


    Please Login or Register  to view this content.
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the DeleteRows macro.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Need better way to delete rows in a database.

    This alternative Do Loop will start at the bottom and go up.

    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Need better way to delete rows in a database.

    s4driver,

    My original reply #7 was for you, and, not for JOHN H. DAVIS.


    Adjust the next line of code in the below macro to point to the correct worksheet:

    Please Login or Register  to view this content.

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Please Login or Register  to view this content.
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the DeleteRows macro.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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