Need macro to remove blank rows

[Guest]

I have a 1-column spreadsheet that contains data in column H. Some rows in
column H contain no data (i.e., its blank). I need help in writing a macro
that, starting with cell H2, will examine each cell in column H and
automatically delete a row where no data exists. The macro would terminate
after reaching row 1000.
I would greatly appreciate any help. Thanks.
Bob

 

[aidan.heritage]

This is ALMOST given in the Excel help file


Set currentCell = Worksheets("Sheet1").Range("H2")
for each cell in range("H2:H1000")
Set nextCell = currentCell.Offset(1, 0)
If Len(currentCell.Value)=0 Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Next

 

[Guest]

Thank you for your help! I sincerely appreciate it. Forgive me for not
checking Excel's Help file. I wasn't aware that it contained code snipets.

It turns out that some of the cells in column H contain a space followed by
some data, while others contain just a single space and nothing else. Is
there a way to also test for cells that contain just a single space (i.e.,
LEN=1) and if true, delete those rows, too?
Thanks again for your help.

Regards, Bob

 

[Guest]

I just realized that if I simply insert:

If Len(currentCell.Value)=1 Then
currentCell.EntireRow.Delete
End If

after the first IF block, that should do the trick. Agree, or is there a
more elegant way to do it?

Thanks again.
Regards, Bob

 

[Guest]

Please ignore my last post. I don't know what I was thinking.
I simply changed:

If Len(currentCell.Value)=1 Then
to
If Len(currentCell.Value)<=1 Then

I don't know why, but when I run the macro once, SOME rows with cells whose
LEN<=1 still remain! When I run the macro a second time, it then catches and
removes those remaining rows.
Do you have any idea why I need to run the macro twice for it to truly
complete the job?
Thanks again.
Bob

 

[Guest]

It turns out that some of the cells in column H contain a space followed by
some data, while others contain just a single space and nothing else. So I
modified the line:
If Len(currentCell.Value)=1 Then
to
If Len(currentCell.Value)<=1 Then

Also, I don't know why, but when I run the macro the first time, SOME rows
with cells whose LEN<=1 still remain! When I run the macro a second time, it
then catches and removes those remaining rows.
Do you have any idea why I need to run the macro twice for it to truly
complete the job?
Thanks again.
Bob

 

[aidan.heritage]

You could try len(trim(currentcell.value)) which would remove any
offending spaces - but the macro SHOULD delete all rows in the range
first time through - you could always set the macro up to run twice
(for DoubleCheck=1 to 2oMacro:Next)

 

[Guest]

I appreciate the suggestion. I will give it a try.
Thanks again for all your help.
Regards, Bob