I need to create a macro that will delete all rows in a column that contain a value of 0. I have tried to use the "If Then" statements in VBA but I just don't have a strong background with the syntax.
Thanks
I need to create a macro that will delete all rows in a column that contain a value of 0. I have tried to use the "If Then" statements in VBA but I just don't have a strong background with the syntax.
Thanks
Can you be a bit more specific, or post an example please?
Are you sure you want the whole row deleted, or just the cell(s) with a value of zero - and the remaining cells 'shifted' up?
Regards
Ted
The data is exported from a master databse. Once in Excel column "A" is the product name, column "B" is the quantity on hand, and column "C" is the price per unit. The export cannot filter on its own to show only those items on hand and their respective rates. Instead I end up every row in column "C" that has a unit rate and many rows in column "B" with a zero. So for this application I am willing to delete the entire row in order to minimize the list.
Thanks
I assume you are OK with VBA add this to a module and run
Suggest testing on a back up first not you original works, just in case
*** Test on back up please****
Kindest regards
Jack in the UK
Code:
Sub delete_Blanks()
rZero = Cells(65536, 1).End(xlUp).Row
For r = rZero To 2 Step -1
If (Cells(r, 1) = 0) Then _
Cells(r, 1).EntireRow.Delete
Next
End Sub
Jack,
Fantasic! It works great. I just need to tinker with it a bit because the data I need it to work from is actually in column "D" row "8" all the way down to row 5000. I have changed the (Cells(r,1) to be (Cells(r,4) to accomadate column D but how do I get the rows correct?
Thanks
Very sorry for any delays getting back to you.
A new version - hope this works OK, remember to test on copy work not real work
Kindest possable regards
Jack in the UK
Code:
Sub delete_Blanks2()
rZero = Cells(5000, 4).End(xlUp).Row
For r = rZero To 8 Step -1
If (Cells(r, 4) = 0) Then _
Cells(r, 4).EntireRow.Delete
Next
End Sub
That's it! This works great. Thanks again for your help.
Good news!!!
Glad you are sorted and my coded script works fine, good luck and many thanks for taking the time to let me know all is working just fine.
Kindest possible regards
Jack in the UK
Don’t have an account yet? Register yourself now and be a part of our community!