Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Deleting potentially lots of rows based on a cell = 0

    This works but takes an inordinate amount of time with 10,000 cells.
    There MUST be a better and faster way to delete all the rows on the sheet if column B contains a 0.

    Sub DeleteRows()
    Dim i As Long

    For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1

    If Cells(i, "B") = 0 Then Rows(i).Delete

    Next i
    End Sub

  2. #2
    New Lounger Nostromov's Avatar
    Join Date
    Oct 2016
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Arrow

    Quote Originally Posted by kweaver View Post
    This works
    ... So, it's a tutorial? :-0

    (Microsoft?!) Office... 2016, OR?? Which version (there are, literally, dozens different ones)? o.0

    How about some screenshots, to go along with this - because not everyone's in-practice with it... Personally, I've lost hang of it LONG AGO; Like, with Office 2003 (Initial release: August 19, 2003; 13 years ago @en.wikipedia.org/wiki/Microsoft_Office_2003)

    ^^ Wouldn't have a single (LOL) clue, where to begin!! Thanks!

    P.S.
    Ugh, I had just posted an answer to this thread... A new comment, here; BUT, nothing is showing - have I missed some sort of a notice, or SOMETHING?! WTH happened?

  3. #3
    Super Moderator satrow's Avatar
    Join Date
    Dec 2009
    Location
    Cardiff, UK
    Posts
    4,491
    Thanks
    284
    Thanked 577 Times in 480 Posts
    Quote Originally Posted by Nostromov View Post
    P.S.
    Ugh, I had just posted an answer to this thread... A new comment, here; BUT, nothing is showing - have I missed some sort of a notice, or SOMETHING?! WTH happened?
    There's anti-spam auto-moderation in place here for new posters, there should have been a brief message flashed up as you posted explaining this. This will expire once you reach the required # of posts.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Surrey, UK
    Posts
    163
    Thanks
    7
    Thanked 40 Times in 35 Posts
    I hope I'm not doing homework! I wondered if the inclusion of the .End element in the for loop might be the cause (if it recalculated after each row delete) but that was not the case (I calculated the last row once outside the loop). Then I tried turning off screen updating (Application.screenupdating=false) and that doubled the speed (for my case). I tried using a union to combine ranges, then deleting in one go at the end (new to me, made no difference).

    My 10-year old Athlon X2-3800 takes 1.5s to delete 10% of the 10,000 rows with SU off, which doesn't seem so bad. How slow is yours? My worksheet only has data in column B, perhaps filling more columns would slow it down.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I don't understand the comment about "homework"...I'm not a student. Well, I'm learning VBA (slowly).

    mngerhold, are you suggesting I remove the "end" or make "end" on a separate line with "sub" on the next line?
    Last edited by kweaver; 2016-10-21 at 17:14.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Surrey, UK
    Posts
    163
    Thanks
    7
    Thanked 40 Times in 35 Posts
    No, the .end bit was about your use of
    For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    within the for call - and I then said it didn't matter. Have you turned off screen updating? And if so, how slow is your code running?

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    ah ha...screen updating might have been the issue. I'll retry.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    If Column B has no text or alpha numeric entries then you could try the following.

    In the worksheet in any empty column (Sale Col "D") put in D1 the formula =10/B1. Copy down to D10000.

    Open VBA and put in the following Code.
    Code:
    Sub RemoveIt ()
    Range(D1:D10000).Select
    Selection.SpecialCells(xlCellTypeFormulas, 16).Select
    Selection.EntireRow.Detete
    End Sub
    Make excel activce run the Macro and it should delete the desired rows rather quickly.

    Hope this helps.

    DuthieT
    Last edited by RetiredGeek; 2016-10-21 at 15:23. Reason: added code tags

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Col B has both text and alphanumeric data. But, screen updating OFF did the trick!

  10. #10
    New Lounger
    Join Date
    Nov 2015
    Posts
    17
    Thanks
    8
    Thanked 2 Times in 2 Posts
    A simpler solution is to filter you data, highlight the rows of data you want, hit F5, select Special, visible cells only, right click, delete rows.

    It's extremely quick and requires no coding.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks, Zeno, but this is for an application that generates a variable number of data rows. It isn't a manual operation. Needs to be automated.

  12. #12
    New Lounger
    Join Date
    Nov 2015
    Posts
    17
    Thanks
    8
    Thanked 2 Times in 2 Posts
    The variable number of rows doesn't affect the method. It's way faster and more flexible than coding in that it can be utilized for any filter criteria in any column. Why make things harder than necessary?

  13. #13
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Surrey, UK
    Posts
    163
    Thanks
    7
    Thanked 40 Times in 35 Posts
    Out of interest I tried Duthiet's selection suggestion: this was fast (twice as fast as the line-by-line method under XP, 10 times as fast (sometimes) under W10). One minor drawback is that if there are no code-16 (error) cells, the special cell selection fails and the code (my code anyway) stops with an obscure error ('400'). On reflection, who cares, as this means there was nothing to do.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Zeno: it's called "clients" ... they want a 1-click solution not a process they have to manually do.

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    This took about a second for 10,000 rows of data where 50% had a 0 in col B

    Code:
    Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim I As Long, strng As String, Lastrow As Long
    Lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    For I = Lastrow To 2 Step -1
        If Cells(I, "B") = 0 Then
            Cells(I, "B").EntireRow.Delete
        End If
    Next I
    End Sub

  16. The Following User Says Thank You to Maudibe For This Useful Post:

    kweaver (2016-10-23)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •