# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Can I conditionally delete row in Excel? (if B3=0 delete row B)

## Badger

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.

----------


## 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.

Badger wrote:
>
> 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.

--

Dave Peterson

----------


## Badger

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.

"Dave Peterson" wrote:

> 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.
>
> Badger wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>

----------


## 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

Badger wrote:
>
> 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.
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > Badger wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

----------


## Badger

Thank you so much!

"Dave Peterson" wrote:

> 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
>
> Badger wrote:
> >
> > 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.
> >
> > "Dave Peterson" wrote:
> >
> > > 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.
> > >
> > > Badger wrote:
> > > >
> > > > 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.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

----------

