Can I conditionally delete row in Excel? (if B3=0 delete row B)

G

Guest

I am trying to delete rows based on conditional statements. For example,
delete row B if cell B3(budget balance) is 0. I am having trouble figuring
this out. Can I incorporate a macro that deletes a row into an "if"
statement? Thanks.
 
D

Dave Peterson

Do you need a macro or can you do it manually?

If manually is ok:

I'd use another column of Cells:

=if(b2=0,"deleteme","keepme")
and drag down.

Then apply data|filter|autofilter to that helper column. And show the Deleteme
rows.

And delete those visible rows.

Remove the filter and delete that helper column.
 
G

Guest

Thanks. I need it to be automated so I think a macro is my only choice
unless I can somehow incorporate a conditional delete row function into a
cell formula.
 
D

Dave Peterson

Maybe something like:

Option Explicit
Sub testme()

Dim rng As Range
Dim wks As Worksheet
Dim myCell As Range
Dim DelRng As Range

Set wks = Worksheets("Sheet1")

With wks
Set rng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In rng.Cells
If IsEmpty(myCell) = False Then
If myCell.Value = 0 Then
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
End If
Next myCell

If DelRng Is Nothing Then
'do nothing
Else
DelRng.Select
'or (after testing!)
'DelRng.EntireRow.Delete
End If

End Sub
 
G

Guest

Thank you so much!

Dave Peterson said:
Maybe something like:

Option Explicit
Sub testme()

Dim rng As Range
Dim wks As Worksheet
Dim myCell As Range
Dim DelRng As Range

Set wks = Worksheets("Sheet1")

With wks
Set rng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In rng.Cells
If IsEmpty(myCell) = False Then
If myCell.Value = 0 Then
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
End If
Next myCell

If DelRng Is Nothing Then
'do nothing
Else
DelRng.Select
'or (after testing!)
'DelRng.EntireRow.Delete
End If

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top