Simple Macro?

[Guest]

Why does the following macro jump to End Sub after it encountered the first
cell with 0 (zero) value instead of deleting the entire row?

Values to be tested are in column B

Sub RemoveRow()
Range("B2:B30").Select
Do Until ActiveCell.Value = 0
If ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

 

[Rob Edwards]

Use the followig...

Sub RemoveRow()
Range("B2:B30").Select
Do Until ActiveCell = ""
If ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

Your version was ending as the Do Until was waiting for the value 0.

Rob Edwards

Always look on the bright side of life!

 

[JE McGimpsey]

Because

Do Until ActiveCell.Value = 0

exits the loop before the rest of the code is executed.

A different way:

Dim rCell As Range
Dim rDelete As Range

For Each rCell In Range("B2:B30")
If rCell.Value = 0 Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
If Not rDelete is Nothing then rDelete.EntireRow.Delete

A bit more code, but much faster.

 

[Guest]

The reason it's jumping to End Sub is because when you selected B2:B30, there
is not just one cell selected. The ActiveCell object was empty.

Try it this way:

Sub RemoveRow()

Dim lRow As Long

For lRow = 30 To 2 Step -1
With ActiveSheet.Cells(lRow, 2)
If .Value = 0 Then
.EntireRow.Delete
End If
End With
Next lRow

End Sub

Note that the above will delete the entire row if the cell in B column has a
0 value, or if it's blank. If you only want to delete rows where there is an
explicit 0 value entered, try it this way:


Sub RemoveRow()

Dim lRow As Long

For lRow = 30 To 2 Step -1
With ActiveSheet.Cells(lRow, 2)
If .Text = "0" Then
.EntireRow.Delete
End If
End With
Next lRow

End Sub


Also note that both loops are going from bottom to the top to make sure you
don't miss any rows.

 

[JE McGimpsey]

Absolutely not!

The ActiveCell ALWAYS is assigned as long as a workbook is Active.

When multiple cells are selected, the ActiveCell is whichever cell has
the focus. It's still populated even if a Shape is selected.

 

[Guest]

Agree! I shouldn't have assumed and said that. I don't use ActiveCell that
often and when I debugged the code, I didn't realize B2 was blank which of
course ActiveCell would be empty. Thanks!