How to delete blank rows

[agentcooter]

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.

 

[Guest]

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.

 

[Dave Peterson]

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.

 

[agentcooter]

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.

 

[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?

 

[agentcooter]

I changed the border format to show the columns and rows on the sheet,
and converted to pdf, at
http://www.knology.net/~cooter/excel/Book2.pdf

Each day has two columns, the time column is formatted as
number/time/1:30 PM. The track name column is formatted as text.

The highlighted rows are the ones I need to delete on this month's
calendar, but it will vary each month.

It doesn't matter if I shift the cells up or delete the rows the boss
just doesn't want any blank rows on the printout.

The code from Chip's site is:

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[agentcooter]

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

 

[Dave Peterson]

Glad you got it working.

(Thanks for posting back)