Sort - max number of columns?

We've recently made a very long spreadsheet with about 80 columns. BAD
things happen!

When people do a lazy sort - that is, ctrl home, Sort and use the populated
defaults, Excel only "selects" 64 columns. Of course this totally pooches
the data and creates disconnects.

No problems when the user selects the whole spreadsheet (by selecting the
upper lefthand corner), sorts and fills in the criteria themselves.

My question is, is this for real or am I imagining it? Is there a limit of
64 columns in Excel on that lazy sort? We're dealing with the workaround, I
just want to know I'm not crazy.

Karen F.

Hi Karen,
I don't like the way that is implemented at all because it almost guarantees
that everybody is going to destroy a lot of data sooner or later and
probably many times over.

I would suggest that you never rely of Excel to pick your sort area based
on a single cell selection which it assumes you will want the current region
(Ctrl+Shift+asterisk). Likewise if you had a few cells selected it would
only sort the selected cells.

Instead use Ctrl+A unless of course you have
Excel 2003 which is also in itself dangerous because the change Ctrl+A.

If you are using Excel 2003 use Ctrl+Shift+SpaceBar instead of Ctrl+A
the select ALL cells and not move the activecell, before invoking the
sort.

What probably happened is that you had an empty column in Column 65

Of course you can sort columns independently of other columns, but that
is not what you want and is not what people wanted to sort when they
suddenly destroy all their data. Use CTRL+Z if you catch your
mistake immediately and hope you didn't have an event macro.

More information on sorting
http://www.mvps.org/dmcritchie/excel/soriting.htm
More information on shortcut keys in Excel
http://www.mvps.org/dmcritchie/excel/shortx2k.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"KarenF" <[email protected]> wrote in message news:[email protected]...
> We've recently made a very long spreadsheet with about 80 columns. BAD
> things happen!
>
> When people do a lazy sort - that is, ctrl home, Sort and use the populated
> defaults, Excel only "selects" 64 columns. Of course this totally pooches
> the data and creates disconnects.
>
> No problems when the user selects the whole spreadsheet (by selecting the
> upper lefthand corner), sorts and fills in the criteria themselves.
>
> My question is, is this for real or am I imagining it? Is there a limit of
> 64 columns in Excel on that lazy sort? We're dealing with the workaround, I
> just want to know I'm not crazy.
>
> Karen F.

Karen,

No, there isn't any limit (at least, none that I am aware of.) Are you sure you don't have a hidden
column that's blank (column 65 or, appropriately enough, column BM)? Excel will sort the
currentregion of the activecell, so any fully blank columns will cause that kind of disconnect.

HTH,
Bernie
MS Excel MVP


"KarenF" <[email protected]> wrote in message
news:[email protected]...
> We've recently made a very long spreadsheet with about 80 columns. BAD
> things happen!
>
> When people do a lazy sort - that is, ctrl home, Sort and use the populated
> defaults, Excel only "selects" 64 columns. Of course this totally pooches
> the data and creates disconnects.
>
> No problems when the user selects the whole spreadsheet (by selecting the
> upper lefthand corner), sorts and fills in the criteria themselves.
>
> My question is, is this for real or am I imagining it? Is there a limit of
> 64 columns in Excel on that lazy sort? We're dealing with the workaround, I
> just want to know I'm not crazy.
>
> Karen F.

It so happens I DO have an empty column 65, with more scattered throughout.
Now it makes perfect sense - Excel doesn't realize there's more data after
that. I solved the problem by adding a blank (" ") as the column header.

Thank you!

"Bernie Deitrick" wrote:

> Karen,
>
> No, there isn't any limit (at least, none that I am aware of.) Are you sure you don't have a hidden
> column that's blank (column 65 or, appropriately enough, column BM)? Excel will sort the
> currentregion of the activecell, so any fully blank columns will cause that kind of disconnect.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "KarenF" <[email protected]> wrote in message
> news:[email protected]...
> > We've recently made a very long spreadsheet with about 80 columns. BAD
> > things happen!
> >
> > When people do a lazy sort - that is, ctrl home, Sort and use the populated
> > defaults, Excel only "selects" 64 columns. Of course this totally pooches
> > the data and creates disconnects.
> >
> > No problems when the user selects the whole spreadsheet (by selecting the
> > upper lefthand corner), sorts and fills in the criteria themselves.
> >
> > My question is, is this for real or am I imagining it? Is there a limit of
> > 64 columns in Excel on that lazy sort? We're dealing with the workaround, I
> > just want to know I'm not crazy.
> >
> > Karen F.
>
>
>

I'd select the whole range and do the sort. I never let excel guess.

It scares me too much.

KarenF wrote:
>
> It so happens I DO have an empty column 65, with more scattered throughout.
> Now it makes perfect sense - Excel doesn't realize there's more data after
> that. I solved the problem by adding a blank (" ") as the column header.
>
> Thank you!
>
> "Bernie Deitrick" wrote:
>
> > Karen,
> >
> > No, there isn't any limit (at least, none that I am aware of.) Are you sure you don't have a hidden
> > column that's blank (column 65 or, appropriately enough, column BM)? Excel will sort the
> > currentregion of the activecell, so any fully blank columns will cause that kind of disconnect.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "KarenF" <[email protected]> wrote in message
> > news:[email protected]...
> > > We've recently made a very long spreadsheet with about 80 columns. BAD
> > > things happen!
> > >
> > > When people do a lazy sort - that is, ctrl home, Sort and use the populated
> > > defaults, Excel only "selects" 64 columns. Of course this totally pooches
> > > the data and creates disconnects.
> > >
> > > No problems when the user selects the whole spreadsheet (by selecting the
> > > upper lefthand corner), sorts and fills in the criteria themselves.
> > >
> > > My question is, is this for real or am I imagining it? Is there a limit of
> > > 64 columns in Excel on that lazy sort? We're dealing with the workaround, I
> > > just want to know I'm not crazy.
> > >
> > > Karen F.
> >
> >
> >

--

Dave Peterson