VBA code to remove zeros but not blanks

[Carter856]

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!

 

[nemmi69]

Do you want the entire row deleted or just in AB and AC?

 

[Carter856]

The entire row - thanks

 

[vcoolio]

Hello Carter856,

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

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.

 

[Carter856]

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,

 

[vcoolio]

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.

 

[vcoolio]

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.