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
    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
    Sub Maybe()
    Application.ScreenUpdating = False
        Range("J1:J" & Range("J" & Rows.Count).End(3)(1).Row).AutoFilter 1, 0
            Range("J2:J" & Range("J" & Rows.Count).End(3)(1).Row).SpecialCells(12).EntireRow.Delete
        ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
    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
    Sub Maybe2()
    Dim lr As Long
    Application.ScreenUpdating = False
    lr = Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp).Row    '<----- Change to actual sheet name
        With Range("J1:J" & lr)
            .Replace 0, "", xlWhole
            .SpecialCells(4).EntireRow.Delete
        End With
    Application.ScreenUpdating = True
    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
    Sub Maybe2()
    Dim lr As Long
    Application.ScreenUpdating = False
    lr = Sheets("Sheet2").Cells(Rows.Count, 10).End(xlUp).Row    '<----- Change to actual sheet name
        With Range("J1:J" & lr)
            .Replace 0, "", xlWhole
            .SpecialCells(4).EntireRow.Delete
        End With
    Application.ScreenUpdating = True
    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
    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!