Delete Entire Row if Cells is Zero in Column J VBA

  • Happy New Year VBA Experts!


    I was wondering if you could help improve the code below. I am trying to delete entire row if value in Column J is zero. The code works but takes very long time over 5 minutes... Thank you so much for your time!



    Sub DelZeroColJ()
    'delete values where zero in value in column J

    Dim lastrow As Long, r As Long
    lastrow = Cells(Rows.Count, "j").End(xlUp).Row

    For r = lastrow To 2 Step -1
    If Cells(r, "j") = o Then
    Rows(r).EntireRow.Delete
    End If
    Next r

    End Sub

  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    Code
    1. If Cells(r, "j") = o Then


    Does that code work at all?
    Looks like the letter o, the letter before the p in the alphabet, instead of a 0 (zero).
    If not, how large is your range?
    Should be done in split second with autofilter


    You could try this on a copy of your workbook. (If it is indeed a zero)

    Code
    1. Sub Maybe()
    2. Application.ScreenUpdating = False
    3. Range("J1:J" & Range("J" & Rows.Count).End(3)(1).Row).AutoFilter 1, 0
    4. Range("J2:J" & Range("J" & Rows.Count).End(3)(1).Row).SpecialCells(12).EntireRow.Delete
    5. ActiveSheet.AutoFilterMode = False
    6. Application.ScreenUpdating = True
    7. End Sub


    Your code works fine after changing the letter o to a numerical zero on my small test range

  • Re: Delete Entire Row if Cells is Zero in Column J VBA



    Thank you for your help!
    I did change to Zero still takes a long time. I have about 30k lines to go through.
    I tried your code as well, works but also takes a long time to go through the lines.
    I am not sure that its what is since I have so many lines to go through or there is a better code. Would you be able to explain what statement belong stands for?
    For r = lastrow To 2 Step -1

  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    That line translates into "from the last row to the first row at a minus one jump.
    So it starts at the last row, checks, move -1 rows (to the next row up), checks, move -1 etc etc
    Someone might have a faster code but the autofilter is the fastest code I can come up with.
    Good luck though.
    You should be able to find some code to add at the start of your macro and again at the end to speed up your code.
    Google is your friend.
    Something like :"How to speed up code in excel?" or "How to speed up my macro in excel?"

  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    Don't know if it'll be any faster but you could try.
    Note: Change references as required (Sheet Name)

    Code
    1. Sub Maybe2()
    2. Dim lr As Long
    3. Application.ScreenUpdating = False
    4. lr = Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp).Row '<----- Change to actual sheet name
    5. With Range("J1:J" & lr)
    6. .Replace 0, "", xlWhole
    7. .SpecialCells(4).EntireRow.Delete
    8. End With
    9. Application.ScreenUpdating = True
    10. End Sub
  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    Another method which could be faster


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    Quote from jolivanes;783263

    Don't know if it'll be any faster but you could try.
    Note: Change references as required (Sheet Name)

    Code
    1. Sub Maybe2()
    2. Dim lr As Long
    3. Application.ScreenUpdating = False
    4. lr = Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp).Row '<----- Change to actual sheet name
    5. With Range("J1:J" & lr)
    6. .Replace 0, "", xlWhole
    7. .SpecialCells(4).EntireRow.Delete
    8. End With
    9. Application.ScreenUpdating = True
    10. End Sub


    THANK YOU so much!!! Last code worked faster! thank you for explanation!

  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    Quote from KjBox;783273

    Another method which could be faster




    Hi ! Thank you so much for responding to my post. However, I keep getting error that subscript out of range for "vbNullString" here.
    Am I missing something? Thank you for your time!

  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    Sorry, my error,code should be

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    Quote from KjBox;783278

    Sorry, my error,code should be


    .SpecialCells(4).EntireRow.Delete -I get this line as no special cells are found error.


    Thank you for all your time and help!!!!

  • Re: Delete Entire Row if Cells is Zero in Column J VBA


    Add this above line of code


    Code
    1. On Error Resume Next

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.