+ Reply to Thread
Results 1 to 3 of 3

Autofilter by Criteria and Delete all Rows

  1. #1
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Autofilter by Criteria and Delete all Rows

    I have a huge macro that I delete various rows based on criteria, it's always worked but for some reason now all the sudden on of them only deletes one row, instead of all visible rows.
    Steps work up until the delete row, which it only deletes the top row instead of all visible.

    See attached example. It's filtering AG for blanks and deleting.

    Here's the code (also included in the file)
    Please Login or Register  to view this content.
    I know it's something simple but it's weird because I have this same macro in my file 6 times and all previous 5 work for other columns..but this one doesn't.
    I suspect it might be because when these blanks appear, all the next columns are also blank so maybe it's only selecting the first row of data, thinking the data is ended..so there must be be a diff way to delete all visible rows based on column A (which always has data)?

    Thanks in advance!

    Edit:

    Would this be the best solution?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by NewYears1978; 05-16-2022 at 12:39 PM.

  2. #2
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,359

    Re: Autofilter by Criteria and Delete all Rows

    It is because after your filter has bee applied column AG is blank, this is the column you are setting the range by:
    Please Login or Register  to view this content.
    You should also call the sheet in each part of the line of code, so:
    Please Login or Register  to view this content.
    Should be:
    Please Login or Register  to view this content.
    Try the below:
    Please Login or Register  to view this content.
    I have used column P to set the end of the range to prove the point.
    Please Login or Register  to view this content.
    Last edited by CheeseSandwich; 05-16-2022 at 12:48 PM.
    If things don't change they stay the same

  3. #3
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Autofilter by Criteria and Delete all Rows

    Sorry for the very late reply, other projects took over.

    But you were right and it fixed my issue, thanks kindly!

+ 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 Row Based on Criteria s using Autofilter
    By Biplab1985 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-03-2018, 11:48 AM
  2. [SOLVED] Delete Rows using Autofilter
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-06-2012, 03:39 PM
  3. Autofilter delete rows but preserve certain columns
    By Quentyn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-07-2011, 03:16 PM
  4. Too many rows to delete by way of Autofilter or what?
    By ron2k_1 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 05-12-2011, 04:18 PM
  5. Macro, autofilter, delete rows outside date range, defined names as criteria
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-30-2011, 04:10 AM
  6. How to use autofilter to delete duplicate rows (2nd criteria) ?
    By Mslady in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2005, 01:36 PM
  7. [SOLVED] delete rows autofilter
    By masterphilch in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-05-2005, 05:06 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