Problems Sorting

I am trying to sort a large worksheet that has blank cells
between each row of data. After I sort, the blank cells
between each row disappears. How can I retain the empty
cells after a sort. It is a matter of clarity on the
printouts. I am using Excel 97.
Thanks for your help

I think your only alternative is to get rid of the blank rows. If you need
to space them out, increase the row height.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"Dave" <dlro...@hotmail.com> wrote in message
news:18b201c24857$5d2ed4a0$9be62ecf@tkmsftngxa03...

Try this:

1. Select your entire range.
2. Press F5 > Special > Blanks > OK
3. Type in something like "zzz" no quotes and press
<ctrl><enter>.
4. Now sort and change the zzz rows to white font, hide
them, etc.

A big assumption here is that there are no blank cells in
the rows with data. If there are, they will be filled
with "zzz".

HTH
Jason
Atlanta, GA

>.
>

Hi Jason

Nice thought!!
But won't the "zzz"' all sort to the end of the list?

--
Regards,
Roger Govier
Technology 4 U
"Jason Morin" <jason...@us.exel.com> wrote in message
news:4e5001c2485a$03feec50$a4e62ecf@tkmsftngxa06...

Yeah, they should...that's what I thought the OP wanted.
She can change it to "aaa" if she wants, or ".00000001".
It really depends what type of data she has (text vs.
value) and where she wants the blank rows to be located at
the after sorting.

Jason

>.
>

I believe she wants the blank rows to stay inbetween each row of data, not
at the top or the bottom.

news:4f8501c24867$267490c0$a4e62ecf@tkmsftngxa06...

Hi Jason

I think the OP wanted to retain the blank rows *between* her items of sorted
data.
Leaving them blank, puts them at the end the same as "zzz".
The other alternatives would put them at the beginning.

I don't think there is any way of doing it - that is until somebody comes up
with a brilliant solution!<bg>

news:4f8501c24867$267490c0$a4e62ecf@tkmsftngxa06...

Dave
Except for John Walkenbach's idea, the only way to do what you want is
to sort your data (thereby loosing your blank rows) then run a macro to
insert the blank rows. And if you're going to do that, you might as well
include the sort in the macro code. Let me know if this would work for you.
Otto

Hey Roger,

Didn't we indirectly address this yesterday?

HIDDEN ROWS DON'T SORT !

With a little set-up ahead of time, this can be done, using CustomViews.

First, go to
<Views> <CustomViews> <Add>
In the NameBox enter
Orig
then <OK>

Now, go through your WS and select all the empty rows.
Right click and choose <Hide>

Then, again go to:
<Views> <CustomViews> <Add>
In the NameBox enter
Sort
then <OK>

Now, Right Click in your toolbar and
<Customize> <Commands> tab
In the Categories window, click on View.
In the Commands window, click on
Custom Views
And drag it to your toolbar.
Then close the Customize window.

You now have 2 views of your WS, with the current view being displayed in
your toolbar.
When it comes time to sort, simply choose the "Sort" view by clicking in the
views window and complete the sort.
Then, just choose the "Orig" view to return to your sorted, empty row
configured WS.

HTH

RD

"Roger Govier" <ro...@technology4u.co.uk> wrote in message
news:O3MrbiGSCHA.1632@tkmsftngp11...

If you need to insert blank rows between each of your
records then you can do something like the following
each time after you sort.

After sorting, in a column next to the list, put the
number 1 in the top row. Put the number 2 in the next
row below. Select those two cells and hover over the
bottom right corner of the selection until the cursor
changes to a narrow plus sign and then double click at
that point. If that doesn't fill numbers down to the
bottom of the list then use one of the other methods for
filling numbers (such as Edit>Fill>Series or drag the
bottom right corner of the selection).

Put the cursor in the same column below the last number
filled and enter the number 1.5 there. Use the menu to
Edit>Fill>Series and enter as the "Stop_Value" the
number seen next to the bottom-most record. Choose the
Column option for "Series_In". The "Step_Value" should
be 1. Click OK and then sort based on this column. All
original records will remain in same order with blank
rows in-between. Delete the numbers if desired.

Before final sort:

Number Your_Records
1 record1
2 record2
3 record3
4 record4
1.5
2.5
3.5

After sorting:

Number Your_Records
1 record1
1.5
2 record2
2.5
3 record3
3.5
4 record4

--Brian Taylor
Refinate (C)2001, 20 free trials.
Click to: Rearrange Cells or Cell Content,
Find Duplicates, Count Items, Import, and more.
www.adetaylor.com

Here's yet another method to insert alternate blank rows. After sorting,
insert a new column A. Select data range A1:Ax. =ROW() Ctrl-entered.
>Data>Subtotals>OK>Add subtotal to 1>OK. Now remove row 2 (grand total)
and remove cols A & B, and >Data>Group>Clear outline.

(but I still prefer John Walkenbach's method)

Cheers, Andrew.

"adetaylor" <ngbt...@adetaylor.com> wrote in message
news:YTv89.15032$LO1.1...@newsread2.prod.itd.earthlink.net...

And you could apply Data|Filter|autofilter

Filter nonblanks, sort, and unfilter.

It's always irritated me that the hidden rows don't sort (I usually want the
whole range to sort)--now I have a reason not to swear! Thanks, RD.

--

Dave Peterson
ec3...@msn.com

Hey Dave,

That's a good one too ... but ... what happens if we put these two ideas
together, and allow the user to first go to Filter, Non- Blanks, and that
then makes it easier and quicker to name the "Sort" CustomView, without
having to select and hide the blank rows?

Regards,

RD

"Dave Peterson" <ec3...@msn.com> wrote in message
news:3D62BF66...@msn.com...

There's only one thing wrong with that scenario. I never use customviews. But
it seems like a nice tip for those who do. <vbg>.

--

Dave Peterson
ec3...@msn.com

Hi Dave and RD

QED

Nice bit of lateral thinking boys!

One thing I had not realised however, when using Autofilter.
If the data are a heading row, followed by alternate lines of data and blank
cells, clicking on the header row and applying Autofilter, does not give the
option of Blank and Non Blank.
Marking the whole range of data first, then applying Autofilter, brings up
the option for Blank and Non Blank.

news:3D62ED6F...@msn.com...

I think excel guesses that you want the range (both columns and rows) until it
sees a break.

That's one reason a lot of people won't select a cell in the table and do a
sort. Sometimes excel tries to guess what I want and it guesses wrong.

Same thing for subtotals/pivottables and more.

--

Dave Peterson
ec3...@msn.com