Macro: Column Sort with Merged Cells

[Lauren Thomas]

Hello
I am working on the attached file that contains this macro. The idea is to sort the columns which contain merged cells via the number in row 6 but it's not quite working.

Please could you help me?

Best wishes
Lauren


Sub ColumnSort()
'
' ColumnSort Macro
'
Application.ScreenUpdating = False
With Sheets("SHOP COMPARISON")
.Range("G:CX").UnMerge
For x = 0 To 32
xx = .Cells(5, 7 + x * 3)
.Cells(2, 7 + x * 3) = xx
.Cells(2, 8 + x * 3) = xx + 0.1
.Cells(2, 9 + x * 3) = xx + 0.2
Next x

With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("G2:CX2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("G2:CX106")
.Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

.Range("G2:CX2").ClearContents
For x = 0 To 32
For y = 3 To 7
.Range(.Cells(y, 7 + x * 3), .Cells(y, 8 + x * 3)).Merge
Next y
Next x
With .Range("G5:CX7")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End With
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

 

[vletm]

Lauren Thomas
1) As Tip 'says': Use tags to embed your VBA Macros
2) ~Middle of Jan 2018 ... everything worked
https://chandoo.org/forum/threads/m...hich-involves-merged-cells.37009/#post-222249If You change number of columns in Your sheet then
same changes should make with code!
3) Have You tried to modify code too?

 

[Ashhu]

when i run the macro i got output something like this (still showed some warning). can you manually show output format which you are looking. also check the spreadsheet attached.

 

[Lauren Thomas]

I know, I think I have completely messed this up! I need to set it up again for the new financial year but we have reduced the number of shops we have so I need to reduce the columns to 32 plus the average and the total at each end. This will stay the same for a year at least!

I also tried to change it from private sub to a macro and then update all the code but I have caused more problems than solved!

 

[vletm]

Lauren Thomas
If number of shops vary ...
then keep option to use all those shops (columns).
Clear shop names which 'no need' and press [ SHOW ].
To see ALL possible shops, press [ ALL ].
Then no need to change ... many things.

I also 'cleaned' that Total (£)-columns formula.
This is that 'original file', which worked in Jan.

 

[bobhc]

Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.
Merge thinks all formulas and functions are related to the left most cell in a merge!!!!!

Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.