Easy Makro for Deleting Rows
 

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 we

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.

Easy Makro for Deleting Rows
 

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.

Easy Makro for Deleting Rows
 

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)

"Tatebana" wrote in message
...
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.

Easy Makro for Deleting Rows
 

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



--
Hope that helps.

Vergel Adriano


"Tatebana" wrote:

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.

Easy Makro for Deleting Rows
 

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" wrote:

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)

"Tatebana" wrote in message
...
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.

Easy Makro for Deleting Rows
 

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" wrote in message
...
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" wrote:

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)

"Tatebana" wrote in message
...
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.

Easy Makro for Deleting Rows
 

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 (" ")



"Vergel Adriano" wrote:

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



--
Hope that helps.

Vergel Adriano


"Tatebana" wrote:

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.

Easy Makro for Deleting Rows
 

Another

If trim(rCell.Value) = "" Then






Tatebana wrote:

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" wrote:

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)

"Tatebana" wrote in message
...
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.





--

Dave Peterson

Easy Makro for Deleting Rows
 

Or...

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



Tatebana wrote:

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 (" ")

"Vergel Adriano" wrote:

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



--
Hope that helps.

Vergel Adriano


"Tatebana" wrote:

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.


--

Dave Peterson