Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.