Looking for VBA code to efficiently Sort of Multiple Columns

[DonEB]

I originally used the following to code to successfully perform my sort.

Range("g7:S1826").Select
ActiveWorkbook.Worksheets("TeamSelection").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("TeamSelection").Sort.SortFields.Add Key:=Range( _
"k7:k1826"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("TeamSelection").Sort.SortFields.Add Key:=Range( _
"R7:R1826"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("TeamSelection").Sort
.SetRange Range("g7:S1826")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

However, I am looking to make my code more efficient and so I am trying to use VBA coding.​

But, when I attempted to perform a sort using the following VBA code I got a "Run-time error '1004': To do this, all the merged cells need to be the same size". I don't understand why.

With Sheets("TeamSelection")

.
.
.
​

Set sortRange = Range("G7:S1826")​

Set keyCell = Range("K7")​

Set keyCell2 = Range("R7")
​

sortRange.Sort Key1:keyCell, Order1:=xlAscending, Key2:=keyCell2, Order2:=xlAscending, Header:=xlYes
.
.
.
​

End With


Any assistance would be appreciated.

Thank you,
Don
​

 

[offthelip]

Merged cells are a real problem to deal with in VBA, they are best avoided if at all possible, and it is usually possible to avoid them. Use format alignment "centre across selection" instead. Then vBA works without any problems

]

 

[DonEB]

I understand that "merged cells" can cause problems but when I review my spreadsheet, my sort range of G7:S1826 does not contain any merged cells. In an attempt to verify this, I selected the entire range, did a right click and opened Format Cells then looked at "merge cells" under Alignment and there is no indication that any cells have been merged. So I am confused by the original error message I received. Is there another way to determine if or where merged cells might exist?

 

[Fluff]

Try running this & see if the msgbox pops up

Sub chk()
   Dim Rng As Range
   For Each Rng In Sheets("TeamSelection").Range("G7:S1826")
      If Rng.MergeCells Then MsgBox Rng.Address
   Next Rng
End Sub

 

[DonEB]

Thank you for sharing this code with me.

I ran the code you provided and NO msgbox popped up. To make sure I understood how it worked, I took it one step further. For testing purposes, I decided to merge a couple cells in the row 1827 (i.e., R1827 with S1827) which is one row outside my original range and then expanded the Range in the subroutine to G7:S1827 and ran the subroutine again. At this point the message box appeared so I now know that I entered the code correctly and that the subroutine works.

Once I reset everything back to the original Range (i.e., G7:S1826), once again, no merged cells were detected.

So, I continue to be confused and wonder if your or anyone else might have any additional thoughts or suggestions on how I can incorporate a more efficient SORT in my macro.

Thank you for your guidance and assistance.
Don

 

[Fluff]

If your original code works I'd stick with it.
In your 2nd code there is a problem

[COLOR=#222222][FONT=Verdana]sortRange.Sort Key1:[/FONT][/COLOR][COLOR=#ff0000][FONT=Verdana]=[/FONT][/COLOR][COLOR=#222222][FONT=Verdana]keyCell[/FONT][/COLOR]

in that you are are missing the highlighted =.
Otherwise I can see no reason why it should fail with that error.

 

[DonEB]

Thank you very much. This must have been the problem as the program seems to be working just fine now. Thanks again!!

One final question as it relates to the use of this more efficient VBA sort code. If I have multiple and distinct sorts within a subroutine, can I use the same key names for each sort (i.e., keycell, keycell2, etc) or should I be using unique names within each sort? I didn't know if there was a simple way to clear out or refresh those fields/variables before each use.

 

[Fluff]

You can use the same variables for each sort, just assign them new ranges as needed.

 

[DonEB]

Excellent! Thank you very much for you help and guidance.

 

[Fluff]

Glad to help & thanks for the feedback