How to delete blank rows
 

As a possibly simpler and more efficient alternative to the macro, have you
tried using Excel's Autofiltering option to select rows? In the Standard
Toolbar, go to Data - Filter - Auto Filter or Advanced Filter. For more
information, look up Autofilter in Excel's online help.

----
Regards,
John Mansfield
http://www.pdbook.com

" wrote:

I am trying to delete blank rows using Chip Pearson's macro; also
tried acouple of addins that delete blank rows.
My problem that the cells are not really blank, apparently they have
zero length strings.
Is there a way to delete zero length string rows, or to convert zero
length string cells to blank cells, and then run the delete blank rows
macro?
The cells' data was achieved via copy/paste from another sheet, which
was achieved from formulas such as =IF(D29="","",ha).
I tried such as =IF(D29="",G32,ha), where G32 is a really blank cell.
But some of the formulas involve time, and the latter formula caused
the time cells to be wrong.
Thanks for any suggestions.

If the zero length strings are values--not formulas, then you could select your
range
edit|replace
what: (leave blank)
with: $$$$$ (some unique string)
replace all

then reverse it
edit|replace
what: $$$$$
with: (leave blank)
replace all

(This cleans up those formulas that evaluated to "" that were converted to
values.)

Alternatively, you could modify Chip's routine.

Depending on what Chip's code was, though.

if isempty(.cells(irow,icol)) then
.rows(irow).delete
end if

could become

if .cells(irow,icol).value = "" then
.rows(irow).delete
end if

You may want to post your code to get a better answer.

wrote:

I am trying to delete blank rows using Chip Pearson's macro; also
tried acouple of addins that delete blank rows.
My problem that the cells are not really blank, apparently they have
zero length strings.
Is there a way to delete zero length string rows, or to convert zero
length string cells to blank cells, and then run the delete blank rows
macro?
The cells' data was achieved via copy/paste from another sheet, which
was achieved from formulas such as =IF(D29="","",ha).
I tried such as =IF(D29="",G32,ha), where G32 is a really blank cell.
But some of the formulas involve time, and the latter formula caused
the time cells to be wrong.
Thanks for any suggestions.

--

Dave Peterson

If you explain how your worksheet is laid out, it would make it easier to help.

Also, post the code that you got from Chip Pearson's site.

I'm not sure if your worksheet is more like the PDF or just 3 columns
(date/time/course).

Do you want to delete the complete row or do you want to shift rows up?

wrote:

Dave, I appreciate your help. If you would , please look at the pdf
document at http://www.knology.net/~cooter/excel/Book1.pdf

It's a monthly listing of thoroughbred tracks that can be wagered on
from VictoryLand. For each day, times are in one column, and track
names are in another. The number of tracks can vary from day to day
and month to month.

What I need to do, is delete the blank rows below the last track at
the bottom of each day, when the entire row is not used. The problem
is that the cells are not really empty. Here's how the cells get
their data.

I have a sheet for each track (48 sheets) that contains the times and
days that the track is running. The data from those is combined into
one sheet, with 48 rows per day. That data is then copied to another
sheet and sorted by time. Then the first 20 rows of each day is
copied and pasted to the sheet that's shown in the pdf file. 48
possible tracks, but we can run a maximum of 20 per day.

I can't figure a way to delete the unused rows at the bottom of each
day.

Thanks again for any help you can give.

On Tue, 26 Apr 2005 07:48:08 -0500, Dave Peterson
wrote:

If the zero length strings are values--not formulas, then you could select your
range
edit|replace
what: (leave blank)
with: $$$$$ (some unique string)
replace all

then reverse it
edit|replace
what: $$$$$
with: (leave blank)
replace all

(This cleans up those formulas that evaluated to "" that were converted to
values.)

Alternatively, you could modify Chip's routine.

Depending on what Chip's code was, though.

if isempty(.cells(irow,icol)) then
.rows(irow).delete
end if

could become

if .cells(irow,icol).value = "" then
.rows(irow).delete
end if

You may want to post your code to get a better answer.

wrote:

I am trying to delete blank rows using Chip Pearson's macro; also
tried acouple of addins that delete blank rows.
My problem that the cells are not really blank, apparently they have
zero length strings.
Is there a way to delete zero length string rows, or to convert zero
length string cells to blank cells, and then run the delete blank rows
macro?
The cells' data was achieved via copy/paste from another sheet, which
was achieved from formulas such as =IF(D29="","",ha).
I tried such as =IF(D29="",G32,ha), where G32 is a really blank cell.
But some of the formulas involve time, and the latter formula caused
the time cells to be wrong.
Thanks for any suggestions.

--

Dave Peterson

Glad you got it working.

(Thanks for posting back)

wrote:

Dave, I'm kicking myself now. This first suggestion you gave me is
exactly what I needed. For some reason, it didn't seem to work when I
first tried it. I must have done it wrong. But I also made a change
back in the formulas on the other sheets, and that may have then
allowed your suggestion here to work. Now it works great. It gets me
back to really blank cells, then Chip's macro deletes the blank rows.

Thank you so much for your help, and I'm sorry I took so long to get
it right.

I'm trying to do a couple of other weird things with the calendar, so
I may be asking for help again in a few days.

Cooter

On Tue, 26 Apr 2005 07:48:08 -0500, Dave Peterson
wrote:

If the zero length strings are values--not formulas, then you could select your
range
edit|replace
what: (leave blank)
with: $$$$$ (some unique string)
replace all

then reverse it
edit|replace
what: $$$$$
with: (leave blank)
replace all

(This cleans up those formulas that evaluated to "" that were converted to
values.)

Alternatively, you could modify Chip's routine.

Depending on what Chip's code was, though.

if isempty(.cells(irow,icol)) then
.rows(irow).delete
end if

could become

if .cells(irow,icol).value = "" then
.rows(irow).delete
end if

You may want to post your code to get a better answer.

wrote:

I am trying to delete blank rows using Chip Pearson's macro; also
tried acouple of addins that delete blank rows.
My problem that the cells are not really blank, apparently they have
zero length strings.
Is there a way to delete zero length string rows, or to convert zero
length string cells to blank cells, and then run the delete blank rows
macro?
The cells' data was achieved via copy/paste from another sheet, which
was achieved from formulas such as =IF(D29="","",ha).
I tried such as =IF(D29="",G32,ha), where G32 is a really blank cell.
But some of the formulas involve time, and the latter formula caused
the time cells to be wrong.
Thanks for any suggestions.

--

Dave Peterson