Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a worksheet in Excel with values in some rows in column B that have been returned as zero (0) but still have a formula in the cell. I want to be able to delete just these rows using a macro.

share|improve this question
Do you want all cells in Column B that contain formulas deleted or only cells whose formula returns 0? – adayzdone May 18 '12 at 2:28

As a software tutor I'm surprised you did not already know how to do this (or is this question just a test?) However, the following code may help:

Sub Macro1()
    ActiveSheet.Range("B:B").AutoFilter Field:=1, Criteria1:="0"
    Selection.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    ActiveSheet.Range("B:B").AutoFilter Field:=1
End Sub  

but I would recommend that (i) you turn on macro recorder and create the macro for yourself and (ii) take extreme care with any macro not created by yourself - in particular one that contains 'Delete' in it!

Note this deletes Row1 so maybe best to insert a row at the top first - also deletes rows where "0" is not derived from a formula.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.