Sorting with merged cells

[liampog]

Hi there

Is there REALLY no way to sort merged cells when the only solution to the spreadsheet I'm designing is to use them?

I have uploaded my spreadsheet to my webspace to make it easier for somebody to look at my spreadsheet and offer me some help if possible.

http://www.pogworld.co.uk/BlankBreakList.xls

The macro in question is "SortAllStaffByShiftStartTime". As you can see from the spreadsheet design, I need the cells in the C:K and L:O to be merged, because Rows 7 to 13 need the column widths to be as they are (value of 2.29).

If I didn't need to use the Rows 7 to 13 for anything, then I would simply change the C and L column widths to something suitable and then sorting would work perfectly.

The staff and shift listings on the right are able to be sorted because I have managed to just use one cell for all the data, but this doesn't work with the merged cells because the dropdown menus then appear too small.

Any ideas anyone?

 

[Michael M]

Hi
Merge cells are the bane of ALL Excel users.
Instead of using merged cells, try this.
Unmerge the cells, Highlight the cells that were previously merged and then Format cells
Select the Alignment Tab and in the Horizontal dropdown select
"Center Across Selection"
It'll do the same thing, but you can work with the sheet much easier

 

[liampog]

Unfortunately it doesnt really help me because

a) I don't want the data to be centred, but left-aligned
b) The data in the the merged cells is selected from a dropdown Data Validation method - by centring across selection, it makes the dropdown lists to small to see as the column width is small at 2.29 points.

If you look at the spreadsheet, you will see what I mean.

 

[Michael M]

Yes, I do see what you mean.
But the situation remains unchanged

There is REALLY no way to sort merged cells

About the only thing you can try is Unmerge, sort and then remerge manually.
Try recording a macro while you do it, but I think it will cause some positional issues.

 

[liampog]

I tried that one... the macro takes ages to run

I think I'm going to have to try to redesign the sheet from scratch.

Thanks for your help Michael, and prompt replies.

 

[Michael M]

How about relocating the dropdown list to above the header and then referencing to the cells you currently have.
You could then use "center across" and still be able to sort

 

[Ruddles]

Sorry, I can't see your worksheet because my organisation in common with many others blocks file-sharing Web sites.

However if the worksheet isn't too big it's entirely possible to write a sort routine which re-orders worksheet rows by deleting and re-inserting them one at a time.

(It's actually possible to do this for any size of worksheet but it's not quick when used on large worksheets!)

 

[Michael M]

Just another point in your macro

I tried that one... the macro takes ages to run

When you created this macro, did you use, at the start of the code

Application.ScreenUpdating = False

AND, if there were a lot of calculations

Application.Calculation = xlCalculationManual