VBA code to remove zeros but not blanks

Carter856

New Member
Joined
Jul 5, 2018
Messages
3
Hi,

I'm hoping to create a VBA macro in excel to automate a task of deleting rows if they contain a zero in columns AB and AC.

However I do not want it to delete blanks.

Can anyone help?

Thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Do you want the entire row deleted or just in AB and AC?
 
Upvote 0
Hello Carter856,

Try the following code placed in a standard module and assigned to a button:-


Code:
Sub DeleteZeros()

With Sheet1.[A1].CurrentRegion
           .AutoFilter 28, 0
           .Offset(1).EntireRow.Delete
           .AutoFilter
           .AutoFilter 29, 0
           .Offset(1).EntireRow.Delete
           .AutoFilter
End With

End Sub

The code assumes:-

- Data starts in column A row 2 with headings in row 1.
- Zeros are in column AB OR column AC



I hope that this helps.

Cheerio,
vcoolio.
 
Last edited:
Upvote 0
Thanks vcoolio,

I'm getting an error message: "Autofilter method of range class failed".

I would also need the code to assume Zeros are in column AB AND column AC.

Is this possible?

Cheers,
 
Upvote 0
Hello Carter856,

In what row does your data start and in which column?
I would also need the code to assume Zeros are in column AB AND column AC.

The code covers this scenario also.

Cheerio,
vcoolio.
 
Upvote 0
Just another thought as to why you may be receiving that error message.

I have used the sheet code in the macro (Sheet1) which may not be the sheet code of the worksheet you are operating from. In the VB Editor, over to the left you'll see your worksheets listed with their names in parentheses. Immediately to the left of these, you'll see the sheet codes (Sheet1, Sheet2 etc...). Change Sheet1 in the macro to whatever your sheet code is.

You could also just change Sheet1 to Activesheet.

Cheerio,
vcoolio.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top