Removing blank rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
Click to expand...

Try the following code:

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



Mit freundlichen Grüssen
Thomas Ramel
 
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?)
 
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
 
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

Thomas Ramel said:
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.
Click to expand...

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)
Click to expand...
 
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,
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
 
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
 
Back
Top