Delete Entire Row if Cells is Zero in Specified Column Range VBA

  • I have a VBA problem - I want to do exactly what was being solved in this thread:



    Delete Entire Row if Cells is Zero in Column J VBA



    However instead of deleting the entire row based on ONE column being zero (in this example, J), I want to delete entire rows which contains zeroes in all cells in a specified range (columns D through AC). For example, if D2 thru AC2 all contain zeroes, then row 2 should be deleted from the sheet. Also, let's say D2 thru Y2 contain zeroes, but Z2 thru AC2 contain numeric values other than 0. In this case, I would NOT want to delete row 2 from the sheet.



    Please let me know how I can change the macro to specify an entire range of columns instead of just one column.

    This is the code I took from the above thread (right now it removes all rows where there is a zero in the 10th column):





    I'm new to this website so don't know how to reach out. If you can message me please do. Thank you!

  • Hello and Welcome to the Forum :)


    The most efficient solution is to use AutoFilter


    Attaching a sample would make things a lot easier ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks for your test file


    Since data is located in Columns A to Z ...


    What are the columns which do need to show zero ?

    All of them ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Yes - what I am saying is, if the entire row shows zeroes, then it should be deleted. Otherwise, the row should not be deleted.

    For example, in this SampleData file, I would want to have row 101 removed, but NOT row 100

  • Hi again,


    Attached is your test file


    You can run the macro called ' pijoDelete ' ;)


    Hope this will help

    Files

    • Test pijo.xlsm

      (49.93 kB, downloaded 8 times, last: )

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • You are welcome :)


    In your real-life sheet, do you have empty columns or some data in columns A,B,C ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • May be the easiest for you is to attach a small file with 5 to 10 records ...BUT with the exact same structure as your working file ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • May be the easiest for you is to attach a small file with 5 to 10 records ...BUT with the exact same structure as your working file ...;)

    You're right - sorry about that. I just attached a file with the exact same structure as my working file called Sample Data 2: SampleData2.xlsx

    I'd also like to automatically delete rows where the data in the Interface column (column B) does not begin with "Gi". For example, rows 2 and 3 would be removed since the value for Interface in B2 and B3 is "VI2112". And I'd like to remove that "total" row at the bottom of the list (where it says 229 of 229 displayed).

    That last bit is just for convenience, to have everything in 1 macro if possible :) not needed though, can just delete that last row manually.

  • Thanks for your new Sample file ...:)


    Will adjust the macro to your specifics ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • An alternative method which would be faster if you have a large data set.


    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.