×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SORTING a Worksheet With Merged Cells Not Same Size
2

SORTING a Worksheet With Merged Cells Not Same Size

SORTING a Worksheet With Merged Cells Not Same Size

(OP)
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.

RE: SORTING a Worksheet With Merged Cells Not Same Size

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") 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

RE: SORTING a Worksheet With Merged Cells Not Same Size

(OP)
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.

RE: SORTING a Worksheet With Merged Cells Not Same Size

(OP)
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.

RE: SORTING a Worksheet With Merged Cells Not Same Size

2
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

RE: SORTING a Worksheet With Merged Cells Not Same Size

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

RE: SORTING a Worksheet With Merged Cells Not Same Size

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

RE: SORTING a Worksheet With Merged Cells Not Same Size

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?

RE: SORTING a Worksheet With Merged Cells Not Same Size

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.

RE: SORTING a Worksheet With Merged Cells Not Same Size

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

RE: SORTING a Worksheet With Merged Cells Not Same Size

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close