I have done this in just one column: selecting all the blanks and then using
Selection.EntireRow.Delete. Works fine.

I am now working with a dataset that is formatted just terribly, full of
headers and subtotals, and the best way to clean it up would be to highlight
the entire range (OK), select all the blanks (OK), and delete the rows (not
OK).

I get no error message, but Excel just skips that line of code and leaves
all the blanks highlighted as nice as you please. Just neglects to delete
them.

Is this because they are scattered in all different columns? When I stop the
macro and go to Excel itself, Edit/delete/row wipes out all the proper rows.
Just can't get it done programmatically, and I don't know why.

selection.EntireRow.Delete

--
Regards,
Tom Ogilvy


"DSSdiva" wrote in message
...
I have done this in just one column: selecting all the blanks and then
using
Selection.EntireRow.Delete. Works fine.

I am now working with a dataset that is formatted just terribly, full of
headers and subtotals, and the best way to clean it up would be to
highlight
the entire range (OK), select all the blanks (OK), and delete the rows
(not
OK).

I get no error message, but Excel just skips that line of code and leaves
all the blanks highlighted as nice as you please. Just neglects to delete
them.

Is this because they are scattered in all different columns? When I stop
the
macro and go to Excel itself, Edit/delete/row wipes out all the proper
rows.
Just can't get it done programmatically, and I don't know why.

Can we see your code?

Would you delete a row if one cell is blank but the rest are not, or does it
need to be all blank?

--
HTH

Bob Phillips

"DSSdiva" wrote in message
...
I have done this in just one column: selecting all the blanks and then
using
Selection.EntireRow.Delete. Works fine.

I am now working with a dataset that is formatted just terribly, full of
headers and subtotals, and the best way to clean it up would be to
highlight
the entire range (OK), select all the blanks (OK), and delete the rows
(not
OK).

I get no error message, but Excel just skips that line of code and leaves
all the blanks highlighted as nice as you please. Just neglects to delete
them.

Is this because they are scattered in all different columns? When I stop
the
macro and go to Excel itself, Edit/delete/row wipes out all the proper
rows.
Just can't get it done programmatically, and I don't know why.

Su this is the segment that is giving me heartburn.

Set homecell = Range("a1")
Set endcell = homecell.SpecialCells(xlLastCell).Offset(0, -10)

Range(homecell, endcell).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

This is to clean up a messed-up looking greenbar report and remove all the
junk rows (a report header, footer, subtotal row, etc.). I do know that every
junk row has at LEAST one blank cell, and the rows I want to keep never have
a blank cell.

This does work just fine if the range is only one column wide (tested).
Making it wider means that I end up with all the blanks highlighted, but they
don't get deleted.

I'd like to not have to select each column one by one to check for blanks
and delete rows, because these reports can have dozens of columns.

Any help is appreciated.

"Bob Phillips" wrote:

Can we see your code?

Would you delete a row if one cell is blank but the rest are not, or does it
need to be all blank?

--
HTH

Bob Phillips

"DSSdiva" wrote in message
...
I have done this in just one column: selecting all the blanks and then
using
Selection.EntireRow.Delete. Works fine.

I am now working with a dataset that is formatted just terribly, full of
headers and subtotals, and the best way to clean it up would be to
highlight
the entire range (OK), select all the blanks (OK), and delete the rows
(not
OK).

I get no error message, but Excel just skips that line of code and leaves
all the blanks highlighted as nice as you please. Just neglects to delete
them.

Is this because they are scattered in all different columns? When I stop
the
macro and go to Excel itself, Edit/delete/row wipes out all the proper
rows.
Just can't get it done programmatically, and I don't know why.

DATA SAMPLE: final product should have no "total" rows, blank rows, or
headings...


05 121-150 34 7.3 153 0.1
06 151-180 48 10.3 12174.54 5
07 181-210 39 8.4 10988.65 4.5
08 211-365 142 30.5 111954.67 45.8
09 366-999 117 25.1 108836.13 44.5
TOTAL 466 244654.99


FIN ANCIAL CLASS C

AGE GRP ACCTS % F/C PATIENT BAL % F/C
01 000-030 4 14.3 0 0
02 031-060 4 14.3 0 0
03 061-090 4 14.3 9830.51 228.0-

Try this

Set homecell = Range("a1")
Set endcell = homecell.SpecialCells(xlLastCell).Offset(0, -3)

Range(homecell, endcell).SpecialCells(xlCellTypeBlanks).Select
For Each cell In Selection.Rows
cell.EntireRow.Delete
Next cell


--
HTH

Bob Phillips

"DSSdiva" wrote in message
...
DATA SAMPLE: final product should have no "total" rows, blank rows, or
headings...


05 121-150 34 7.3 153 0.1
06 151-180 48 10.3 12174.54 5
07 181-210 39 8.4 10988.65 4.5
08 211-365 142 30.5 111954.67 45.8
09 366-999 117 25.1 108836.13 44.5
TOTAL 466 244654.99


FIN ANCIAL CLASS C

AGE GRP ACCTS % F/C PATIENT BAL % F/C
01 000-030 4 14.3 0 0
02 031-060 4 14.3 0 0
03 061-090 4 14.3 9830.51 228.0-

Woo!

I tried it your way, but it ended up deleting a bit too much: if cell B1 and
C1 were both blank, it tested B1 and deleted row 1. But cell C1 was still
selected (even if it now held the value from the "old" row 2) and was
therefore deleted as well.

BUT the lightbulb went on and I tweaked it: works like a charm.

THANKS for setting me on the right path!

Set homecell = Range("a1")
Set endcell = homecell.SpecialCells(xlLastCell).Offset(0, -5)

Set testrange = Range(homecell, endcell)
'testrange.Select

For Each CELL In testrange
'CELL.Select
If CELL.Value = "" Then
CELL.EntireRow.Delete
End If
Next CELL



"Bob Phillips" wrote:

Try this

Set homecell = Range("a1")
Set endcell = homecell.SpecialCells(xlLastCell).Offset(0, -3)

Range(homecell, endcell).SpecialCells(xlCellTypeBlanks).Select
For Each cell In Selection.Rows
cell.EntireRow.Delete
Next cell


--
HTH

Bob Phillips

"DSSdiva" wrote in message
...
DATA SAMPLE: final product should have no "total" rows, blank rows, or
headings...


05 121-150 34 7.3 153 0.1
06 151-180 48 10.3 12174.54 5
07 181-210 39 8.4 10988.65 4.5
08 211-365 142 30.5 111954.67 45.8
09 366-999 117 25.1 108836.13 44.5
TOTAL 466 244654.99


FIN ANCIAL CLASS C

AGE GRP ACCTS % F/C PATIENT BAL % F/C
01 000-030 4 14.3 0 0
02 031-060 4 14.3 0 0
03 061-090 4 14.3 9830.51 228.0-