Removing blank rows

[Bob]

I have a 1-column spreadsheet that contains data in column H (with a heading
in cell H1). Some rows in column H contain no data or a single space.
Starting with cell H2, I needed a macro that would examine each cell in
column H and automatically delete a row where no data or only 1 space exists.
The macro would terminate after reaching row 1,000. Someone else in this
forum proposed the following macro:

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

Unfortunately, when I run the macro the first time, SOME rows with cells
whose LEN<=1 still remains! When I run the macro a second time, it then
catches and removes those remaining rows. Does anyone have any idea why I
need to run the aforementioned macro twice for it to truly complete the job?

I would greatly appreciate any help. Thanks.

Bob

 

[Ron de Bruin]

Always start on the bottom and go up when you try to delete rows Bob

See
http://www.rondebruin.nl/delete.htm

 

[Thomas Ramel]

Grüezi Bob

Bob schrieb am 20.06.2006

I have a 1-column spreadsheet that contains data in column H (with a heading
in cell H1). Some rows in column H contain no data or a single space.
Starting with cell H2, I needed a macro that would examine each cell in
column H and automatically delete a row where no data or only 1 space exists.

I would greatly appreciate any help.

Try the following code:

With Range("H:H")
.Replace " ", "", xlWhole
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With



Mit freundlichen Grüssen
Thomas Ramel

 

[markwalling]

i had a similar situation and applied the following logic:

With Range("h2:h1000")
'row i am deleting
index = '''''''

..Cells(index + 1, 1).ClearContents

'bubble up the rest of the materials
For last = 1 To 998
If .Cells(last , 1) = "" or .cells(last,1)=" " Then
For r = last To 998
If .Cells(r + 1, 1) <> "" Then
.Cells(last + 1, 1) = .Cells(r + 1, 1)
.Cells(r + 1, 1).ClearContents
Exit For
End If
Next r
End If
Next last
End With

i also would be interested if someone else had a more efficient way of
doing this (recusrisve function?)

 

[Bob]

Ron,
Thanks for your help! I sincerely appreciate it. Unfortunately, I am a
novice programmer, and after reviewing the info on your site, I was
overwhelmed. To be candid, I was hoping for someone to simply modify the
code below so it would work the first time (rather than having to run it
twice).
Regards, Bob

 

[Bob]

Thomas,
Very slick! I gave your macro a try and was amazed at how fast it works.
In fact, it even caught cells with 2 blank spaces. Thank you! I sincerely
appreciate all your help.
Regards, Bob

Grüezi Bob

Bob schrieb am 20.06.2006

I have a 1-column spreadsheet that contains data in column H (with a heading
in cell H1). Some rows in column H contain no data or a single space.
Starting with cell H2, I needed a macro that would examine each cell in
column H and automatically delete a row where no data or only 1 space exists.

I would greatly appreciate any help.

Try the following code:

With Range("H:H")
.Replace " ", "", xlWhole
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With



Mit freundlichen Grüssen
Thomas Ramel

--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]
Microsoft Excel - Die ExpertenTipps:
(http://tinyurl.com/9ov3l und http://tinyurl.com/cmned)

 

[Bob]

Mark,
Thanks for your help! Check out Thomas Ramel's solution in the thread
above. It is extremely concise. I tried it out on my data and was amazed at
how fast it worked! I'm going to have to study his code closely to
understand how it works.
Regards, Bob

 

[Ron de Bruin]

Hi Bob

Be aware that SpecialCells errors when there are no blanks
Use a on error

Also there is a limit

See
http://www.rondebruin.nl/specialcells.htm

 

[Bob]

Ron,
Thanks for the heads-up. Fortunately, the range of my data never exceeds
more than a 1,000 rows, and it's only 1-column wide.
With respect to adding an OnError check, I can certainly do that, but my
data always has blank cells. So do I really need to add the check? If so,
where in Ramel's code would I do so? Thanks for your help.
Regards, Bob

 

[Ron de Bruin]

Hi Bob

With Range("H:H")
.Replace " ", "", xlWhole
On Error Resume Next 'In case there are no blank cells
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With

 

[Bob]

Ron,
Thanks!!! I sincerely appreciate your help.
Regards, Bob