Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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()
    Columns("B:B").Select
    Selection.AutoFilter
    ActiveSheet.Range("B:B").AutoFilter Field:=1, Criteria1:="0"
    Selection.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    ActiveSheet.Range("B:B").AutoFilter Field:=1
    Selection.AutoFilter
    Range("B1").Select
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .