+ Reply to Thread
Results 1 to 7 of 7

Delete certain rows in UsedRange

  1. #1
    Forum Contributor
    Join Date
    05-30-2021
    Location
    Colorado Springs
    MS-Off Ver
    Microsoft 365
    Posts
    173

    Delete certain rows in UsedRange

    Hello my fellow Excel enthusiasts and experts!

    I am in need of a macro that will analyze ActiveSheet and then select and delete entire rows that are implicated in that sheet's "Used Range" by this criteria:
    1. IF Cells, Range A2:U2 (and downwards) are all blank, delete row.
    2. IF Cells, Range C2:U2 (and downwards) are all blank, delete row.
    3. IF Cells, Range B2:U2 (and downwards) are all blank, delete row.

    Cells in A2:I2 and downwards are "General" Format.

    Cells in J2:U2 and downwards are .NumberFormat = "#,##0.00"

    Also, there is an expanse of entirely empty rows (A:XFD) before rows with some population in A:U starts back up.

    I have tried some things that aren't working and really appreciate any help. Thank you for considering.
    Last edited by kyle4570; 06-12-2022 at 07:01 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Delete certain rows in UsedRange

    If #2 is true, then #1 and #3 are also true. So #1 and #3 don't matter. And you are just talking about row 2. Is that definitely what you meant?

    As always, a sample file would expedite understanding what you want to do. See yellow banner at the top of the page.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    05-30-2021
    Location
    Colorado Springs
    MS-Off Ver
    Microsoft 365
    Posts
    173

    Re: Delete certain rows in UsedRange

    Quote Originally Posted by 6StringJazzer View Post
    If #2 is true, then #1 and #3 are also true. So #1 and #3 don't matter. And you are just talking about row 2. Is that definitely what you meant?

    As always, a sample file would expedite understanding what you want to do. See yellow banner at the top of the page.
    I see what you are saying, so thanks for helping me to clarify. Truly, the qualifier for deleting a row is IF all cells in the J:U (starting in Row 2) are null, then the row needs to be deleted.

    With the attached, I have placed some examples to represent how the raw data is and then highlighted the rows that would be deleted.
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Delete certain rows in UsedRange

    Here is the code that will look at the active sheet and delete all rows that have no data in columns J:U starting in row 2.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Delete certain rows in UsedRange

    Another Option...Credit to jindon...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  6. #6
    Forum Contributor
    Join Date
    05-30-2021
    Location
    Colorado Springs
    MS-Off Ver
    Microsoft 365
    Posts
    173

    Re: Delete certain rows in UsedRange

    Excellent!

    6StringJazzer, thank you.

    sintek (and jindon), thank you as well. Always good to have an additional tool or method in the holster.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Delete certain rows in UsedRange

    You're welcome, thanks for the rep.

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

+ 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] Usedrange has more rows than actual dataset
    By MaartenRo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2021, 01:42 AM
  2. [SOLVED] Delete empty rows - UsedRange - Not working
    By cavo789 in forum Excel General
    Replies: 2
    Last Post: 06-12-2017, 03:50 AM
  3. [SOLVED] usedrange doesn't reset when using 'application.activesheet.usedrange'
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-16-2015, 10:39 PM
  4. activesheet.usedrange.rows.count
    By kisanvikas2015 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2015, 09:34 AM
  5. copy paste usedrange data and delete duplicate rows
    By mani_bbc05 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-09-2013, 02:12 PM
  6. How can i use UsedRange.Rows.Count if any blank rows embeded into the sheet
    By amarendra19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-20-2011, 11:52 AM
  7. ActiveSheet.UsedRange.Rows.Count
    By Kjaer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2007, 05:51 AM

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