SORTING a Worksheet With Merged Cells Not Same Size 2

[CHD01]

I have a speradsheet where rows contain merged cells and different width rows and columns. When I try to SORT the data by the overall score in my last column, I get the following error message: "This operation requires the merged cells to be identically sized" Can anyone suggest a way around this where I can maintain the formatting that I presently have for the spreadsheet? Alternatively, is there a way to easily make all the cells, rows and columns same size so that I can sort and then go back to the formated sheet? The more you learn, the less you are certain of.

 

[jproj]

CHD01:

This is rather tedious (especially if you have to do it multiple times), but I did get it to work...

1) Copy and paste the field you want to format to an empty sheet.
2) With the field selected, Click on "Format" --> "Cells" (or press Ctrl and 1) and click on the "Alignment" tab. At the bottom of the window, (under "Text Control&quot uncheck the "Merge Cells" box.
3) Sort your data.
4) Go back to the original sheet, select the original data, and copy it again.
5) Go to the sorted data and select the first cell in your sorted data. Click on "Edit" --> "Paste Special" and choose "Formats".
6) If you want the sorted data back on the original sheet, in place of the original data, cut and paste.

Although this will sort the data, the location of the merged cells will not be affected (the cell formats will not be sorted with the original contents).

I'm interested to see if anyone has a way to sort the cell format with the cell value. I'm sure one could do it with VBA...

Good Luck!

jproj

 

[CHD01]

To: JPROJ

Thanks. I tried what you suggested and got it to work - although what I actually did may be somewhat different. I don't think its too tedious (except for the last step to recover the merged cell formatting) and will use it in the future. Here's what I wound up doing - is it any different from what you described?

1. Create the new spreadsheet
2. Copy and paste entire sheet to the new spreadsheet
3. Go to format and while the entire sheet is highlighted, un-check the merge cells box
4. Sort my data
5. Went back to original and copied only the column and row block of cells that contained merged cells and then pasted that onto the sorted new sheet. (I tried to do this with just a single click on the entire sheet and that was not permitteed by excel - had to go to just a field in the entire sheet)
6. Done - I just used the new sheet as the new form, saving it to file. The more you learn, the less you are certain of.

 

[CHD01]

Need to correct step 5, this was a paste-special for format only.

Thanks again, JPROJ The more you learn, the less you are certain of.

 

[dison]

Just to add my two cents:

I have never found any use for merging cells. Merge prevents changing column widths and causes the sorting problems mentioned above. A better option is to use different formatting techniques, such as center across selection and breaking up text to give your spreadsheet your desired look. This way you don't have to go through the unmerge remerge or paste format steps every time you want to use your information.

The first thing I do when I use spreadsheets written by others that use merged cells is reformat them using center across selection for alignment or separate text into different rows to maintain overall width without wrapping in just a single cell. ~dison

 

[melone]

How do you center something across cells without merging them? Say I want to center something across 4 colums, how do i do it?

 

[dison]

The text you want to see should be typed in the leftmost column. Select the four cells. Go to format cells - horizontal alignment - center across selection.

As long as nothing else is typed in the other three cells, this will do the job. ~dison

 

[macajm]

I thoroughly agree with ~dison
Use "Centre across selection" wherever possible instead of merge cells. I even keep an "UnMerge Cells" button on my toolbar to use with imported spreadsheets.

Does anyone know how i can get a "Centre across selection" button to add to my toolbar?

 

[PaycheckSlave]

macajm,

I'm not claiming this is the best way, but I have the following macro in my PersonalMacrosWorkbook.

Sub CenterAcrossSelection()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
End Sub

Then I added a custom button to the formatting toolbar and assigned this macro. There may be a better way, but it works.

 

[Biscuit]

I understand how the centre across cells works horizontally (rows), but is there anything similar for merged cells in a column.

 

[melone]

<Ctrl>-1 -> Alignment -> pick whatever you want to do