Easy Makro for Deleting Rows

[Tatebana]

In response to my my question with this -what I thought- simple macro I got
two answers, that worked perfectly, but only on one small worksheet. When I
tried to apply them on a bigger sheet with 1800 rows (and changinge the
macro accordingly) neither would work!

(It would also not work for the "original" Range B2:B30, i.e. it did not
delete any rows in that range.)

The macros were:

Sub RemoveRow_1() by JE McGimpsey

Dim rCell As Range
Dim rDelete As Range

For Each rCell In Range("B2:B30") (Changed to "B2:B1800")
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

End Sub


and


Sub RemoveRow_2() by Vergel Adriano

Application.ScreenUpdating = False -> added by me: correct?

Dim kRow As Long

For kRow = 30 To 2 Step -1 (changed to 1800 To 2)

With ActiveSheet.Cells(kRow, 2) <- is this for Col. B ?
If .Value = 0 Then
.EntireRow.Delete
End If
End With

Next kRow

End Sub

Values were always in Column B, but I guess they could be in any one.

 

[Tatebana]

Oh my God! As I copied the data from the net, I oversaw that the "empty"
cells contained blanks...! Shame on me.

That, however could lead to the question: Can these blanks be detected? I
could replace them first, of course with a simple formula, but that would
also eliminate the blanks in text, where they are necessary.

 

[Bob Phillips]

test for "", or do you actually mean a space, in which case test for " "

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Vergel Adriano]

Hi,

in the line where you check for the value of the cell, test the Text
property if it's equal to "0" instead. For example, in the code from JE
McGimpsey, instead of this line

If rCell.Value = 0 Then

try it this way

If rCell.Text= "0" Then

And in the code from me, you would use


If .Text = "0" Then

 

[Tatebana]

The problem is: some cells contain " " (1 spaces, others " "(2), others "
"(3), etc.

I can get away with it with replacing all spaces and later put them in again.

 

[Bob Phillips]

Try testing Len(Trim(value)) for 0.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tatebana]

No, unfortunately it does not work, probably because some cells contain 1, 2
or three spaces, others contain 0. The row with 0 should also be deleted.

One would, I guess, have to test for 0, empty cells ("") and spaces (" ")

 

[Dave Peterson]

Another

If trim(rCell.Value) = "" Then

The problem is: some cells contain " " (1 spaces, others " "(2), others "
"(3), etc.

I can get away with it with replacing all spaces and later put them in again.

 

[Dave Peterson]

Or...

If trim(rCell.Value) = "" _
rcell.value = 0 Then