Conditional row deletion based on value in a row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig - 28 Dec 2005 15:21 GMT

I am trying to create a macro that will delete all rows in a worksheet that
have the value 0 in column B. The macro should only delete rows where the
value equals 0, and not rows where the value contains 0 (for example, 10).

Ideally I'd like to have this in a form that I could edit in the future, so
that if down the road I wanted to change the macro to delete all rows where
column F contained the value "Elephant", I could do so without starting from
scratch.
thank you,
Craig

Reply to this Message

Bob Umlas - 28 Dec 2005 15:27 GMT

Try this (untested)
Sub DeleteThem()
For i=Range("B65536").end(xlup).row to 1 step -1
if cells(i,2).value = 0 then rows(i).Delete
Next
End Sub

Sub DeleteElephantfromF()
For i=Range("F65536").end(xlup).row to 1 step -1
if cells(i,6).value = "Elephant" then rows(i).Delete
Next
End Sub

HTH
Bob Umlas
Excel MVP

> I am trying to create a macro that will delete all rows in a worksheet that
> have the value 0 in column B. The macro should only delete rows where the
[quoted text clipped - 6 lines]
> thank you,
> Craig

Reply to this Message

Chip Pearson - 28 Dec 2005 15:28 GMT

Craig,

Try code like the following:

Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = 0 Then
Rows(RowNdx).Delete
End If
Next RowNdx

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

>I am trying to create a macro that will delete all rows in a
>worksheet that
[quoted text clipped - 12 lines]
> thank you,
> Craig

Reply to this Message

Dave Peterson - 28 Dec 2005 15:30 GMT

One way is to apply Data|Filter|Autofilter to that column.

Then filtering to show the cells with the value you want to delete.

Then deleting those visible rows.

Record a macro when you do it and you'll have code you can modify.

Signature

Dave Peterson

Reply to this Message