Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort the entire region (2002 SP3)

    I am making a workbook to be distributed to a number of end-users. Several of the worksheets contain Lists of items, anywhere from 5-30 columns and up to several hundred rows. Not all the columns are fully populated, although the left-most column (Unique name) is fully populated, and there is Header row across the top. The users should be allowed to sort the list, preferably using the toolbar Sort buttons.

    If the cursor is in the first column, and the user clicks one of the toolbar Sort buttons, then the entire List is sorted. However, if the cursor happens to be in an area that is not fully populated, then Excel expands the current cell to the CurrentRegion, and just sorts that region. This messes up the List...

    I suppose I must write a macro to select the entire list and sort by the current column, but what is to stop the user from inadvertently clicking the toolbar Sort button? How does one work around that problem?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort the entire region (2002 SP3)

    This might be a "silly question", but why do you have entire columns in a database that are empty? Why not just remove the blank columns which are causing the problem? All the "builtin" features which "guess" at ranges, go to empty rows/columns as the definition of the range.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort the entire region (2002 SP3)

    Not so silly... made me go back and ponder whether I was on the right track.

    But the problem isn't with entirely blank columns, it's with areas "isolated" by surrounding blank cells. Some of the items do not exist for all records, and that's legitimate within the data definition. If the user places the cursor in one of those isolated areas just by happenstance, then executing a sort will corrupt the list. I suppose that one solution could be to fill the empty cells with zeros.

    Such are the problems of trying to shoehorn a database requirement into a spreadsheet. I have reasons for hosting the application exclusively in Excel, rather than attaching a spreadsheet. It's a matter of understanding the tradeoffs.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •