Deleting Rows in Excel 2003
 

Is there a macro that can delete the rows where the value in range a, range b
and range c is equal to zero? In my example below only row 6 would be
deleted.

Range A Range B Range C
Row 1 22 17 18
Row 2 9 10 24
Row 3 1 23 25
Row 4 0 0 26
Row 5 0 29 0
Row 6 0 0 0
Row 7 0 20 8
Row 8 30 28 7
Row 9 21 4 5
Row 10 6 3 14

Deleting Rows in Excel 2003
 

There could be.

But how about an alternative.

Add a new column (D) and put:
=countif(a1:c1,0)
and drag down

then apply data|Filter|autofilter
filter to show the 3's.
delete those visible rows
and delete that column.


Gilbert wrote:

Is there a macro that can delete the rows where the value in range a, range b
and range c is equal to zero? In my example below only row 6 would be
deleted.

Range A Range B Range C
Row 1 22 17 18
Row 2 9 10 24
Row 3 1 23 25
Row 4 0 0 26
Row 5 0 29 0
Row 6 0 0 0
Row 7 0 20 8
Row 8 30 28 7
Row 9 21 4 5
Row 10 6 3 14

--

Dave Peterson

Deleting Rows in Excel 2003
 

Thanks Dave, that's pretty clever.

"Dave Peterson" wrote:

There could be.

But how about an alternative.

Add a new column (D) and put:
=countif(a1:c1,0)
and drag down

then apply data|Filter|autofilter
filter to show the 3's.
delete those visible rows
and delete that column.


Gilbert wrote:

Is there a macro that can delete the rows where the value in range a, range b
and range c is equal to zero? In my example below only row 6 would be
deleted.

Range A Range B Range C
Row 1 22 17 18
Row 2 9 10 24
Row 3 1 23 25
Row 4 0 0 26
Row 5 0 29 0
Row 6 0 0 0
Row 7 0 20 8
Row 8 30 28 7
Row 9 21 4 5
Row 10 6 3 14

--

Dave Peterson

Deleting Rows in Excel 2003
 

It's actually a very efficient way to deal with such a situation in many
cases. If you still want a Sub that would do specifically what you need for
some future use, or if this is an operation that either you or someone
unfamiliar with the process might 'need' to use, this should do the trick for
you. Copy and paste into a regular code module, will work on the sheet that
is active when the macro is executed.

Sub RemoveZeroValueRows()
'
'deletes rows with
'value of zero in columns
'A, B and C
'stops at first row with
'empty cells in those
'same columns.
'
Dim Roffset As Long

Range("A1").Select
Do Until IsEmpty(ActiveCell.Offset(Roffset, 0)) And _
IsEmpty(ActiveCell.Offset(Roffset, 1)) And _
IsEmpty(ActiveCell.Offset(Roffset, 2))
If ActiveCell.Offset(Roffset, 0) = 0 And _
ActiveCell.Offset(Roffset, 1) = 0 And _
ActiveCell.Offset(Roffset, 2) = 0 Then
Rows(ActiveCell.Offset(Roffset, 0).Row & ":" & _
ActiveCell.Offset(Roffset, 0).Row).Delete
Roffset = Roffset - 1
End If
Roffset = Roffset + 1
Loop

End Sub

"Gilbert" wrote:

Thanks Dave, that's pretty clever.

"Dave Peterson" wrote:

There could be.

But how about an alternative.

Add a new column (D) and put:
=countif(a1:c1,0)
and drag down

then apply data|Filter|autofilter
filter to show the 3's.
delete those visible rows
and delete that column.


Gilbert wrote:

Is there a macro that can delete the rows where the value in range a, range b
and range c is equal to zero? In my example below only row 6 would be
deleted.

Range A Range B Range C
Row 1 22 17 18
Row 2 9 10 24
Row 3 1 23 25
Row 4 0 0 26
Row 5 0 29 0
Row 6 0 0 0
Row 7 0 20 8
Row 8 30 28 7
Row 9 21 4 5
Row 10 6 3 14

--

Dave Peterson