Sorting a range loses formatted borders

  • Thread starter Thread starter Mick Jennings
  • Start date Start date
M

Mick Jennings

Hi all,

Recently started new job where the standard is Excel 2003 SP2.

I have a worksheet which is automatically populated and formatted from VBA,
based on data extracted from a directory-full of spreadsheets. All works ok,
except when I sort the resulting worksheet the borders don't move. Patterns
etc move fine, but the borders stay where they were, leaving an ugly mess.

Exactly the same happens whether the sort is triggered from code or from the
menus.

Anyone seen this before ?
 
Excel does not include borders when sorting, no matter what Excel version you use.
Also, conditionally formatted colors do not sort.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - two dozen ways to sort with "Special Sort")




"Mick Jennings"
wrote in message
Hi all,
Recently started new job where the standard is Excel 2003 SP2.

I have a worksheet which is automatically populated and formatted from VBA,
based on data extracted from a directory-full of spreadsheets. All works ok,
except when I sort the resulting worksheet the borders don't move. Patterns
etc move fine, but the borders stay where they were, leaving an ugly mess.

Exactly the same happens whether the sort is triggered from code or from the
menus.
Anyone seen this before ?
 
Thanks Jim. A few separate experiments confirms that. Seems a major oversight
to me, but at least I know.
 
Maybe an oversight; ...MS hears a different drummer. <g>
My Special Sort add-in didn't overlook it.
Direct download at my website for the free 3 week trial.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Mick Jennings"
wrote in message
Thanks Jim.
A few separate experiments confirms that. Seems a major oversight
to me, but at least I know.
 
Hi Mick,

During sorting OpenOffice doesn't leave borders in the same place, but sorts them along with the rest of cell content.

This may not help you as I am not using an automated setup, Visual Basic etc. I am using Excel 2003, so those of you using more recent versions of Excel may need to save using 'save as' and change the version to an earlier version that OpenOffice opens.

I made a second copy of my working file by clicking once on the file in ‘my documents’, then pressing CTR C (copy) followed by CTR V (paste). The reason for this 2nd copy, I wasn’t confident I would be happy with result so I created a backup.

Then you opened the ‘copy’ in OpenOffice, then sorted the content, then saved it in original format so Excel can open it later. I am not confident that all your formats will translate, so a backup is important. My objective with making a back up was, I can copy just the work sheet where the sort occurred back into the original documents so to carry out as few edits as possible in open office.

Disadvantages I noticed are:-
OpenOffice doesn’t support patterns in cells, during the opening process the patterns are converted to a darker shade of the original back ground colour in the cell. The converted colour is unlikely to be a default colour in OpenOffice and can’t be added as back ground colour to another cell if you wish to continue editing in OpenOffice.

OpenOffice is a free download. Hope this is useful to someone.

Regards Rodney
 
Back
Top