Removing blank rows

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

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

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

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" <[email protected]> wrote in message news:[email protected]...
>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.

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)

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


"Ron de Bruin" wrote:

> Always start on the bottom and go up when you try to delete rows Bob
>
> See
> http://www.rondebruin.nl/delete.htm
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "Bob" <[email protected]> wrote in message news:[email protected]...
> >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
> >
>
>
>

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

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

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

"[email protected]" wrote:

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

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


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" <[email protected]> wrote in message news:[email protected]...
> 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
>
> "[email protected]" wrote:
>
>> 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 wrote:
>> > 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,
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" wrote:

> 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
>
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "Bob" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
> > "[email protected]" wrote:
> >
> >> 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 wrote:
> >> > 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
> >>
> >>
>
>
>

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


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Bob" <[email protected]> wrote in message news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>> --
>> Regards Ron De Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Bob" <[email protected]> wrote in message news:[email protected]...
>> > 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
>> >
>> > "[email protected]" wrote:
>> >
>> >> 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 wrote:
>> >> > 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,
Thanks!!! I sincerely appreciate your help.
Regards, Bob


"Ron de Bruin" wrote:

> 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
>
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "Bob" <[email protected]> wrote in message news:[email protected]...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >> --
> >> Regards Ron De Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Bob" <[email protected]> wrote in message news:[email protected]...
> >> > 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
> >> >
> >> > "[email protected]" wrote:
> >> >
> >> >> 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 wrote:
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>