Need macro to remove blank rows

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

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


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

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


"[email protected]" wrote:

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

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

"Bob" wrote:

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

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



"Bob" wrote:

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

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


"[email protected]" wrote:

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

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 2:DoMacro:Next)
Bob wrote:
> 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
>
>
> "[email protected]" wrote:
>
> > 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
> >
> >
> > Bob wrote:
> > > 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
> >
> >

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


"[email protected]" wrote:

> 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 2:DoMacro:Next)
> Bob wrote:
> > 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
> >
> >
> > "[email protected]" wrote:
> >
> > > 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
> > >
> > >
> > > Bob wrote:
> > > > 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
> > >
> > >
>
>