VBA - Sort groups of rows separately

[skpper]

Hi,

I have 20 groups of rows that I would like to sort by column G (cell colour) and column K (cell value).

The first group starts at row 19 (between G19:K42) and each group is spaced 6 rows apart (e.g. second group located between G49: K72).

I am trying to fit in a dynamic referencing/loop formula as shown below, but there are issues with the syntax. Would appreciate help with this.


Sub SortPM()
'
Dim i As Long
Dim currentrange As Range
Dim column1range As Range
Dim column2range As Range

For i = 0 To 19

Set currentrange = Range("G" & "(19 + i*30)", "K" & "(42 + i*30)") 'Does not work (range of object - 'Global' failed)
Set column1range = Range("G" & "(19 + i*30)", "G" & "(42 + i*30)")
Set column2range = Range("K" & "(19 + i*30)", "K" & "(42 + i*30)")

currentrange.Select

ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 192, 0)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 230, 153)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 242, 204)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(84, _
150, 53)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add Key:=Range( _
column2range), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Priority map").Sort
.SetRange currentrange
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Next i

End Sub

 

[gravanoc]

Hi Skipper. On my phone right now, but it appears you are quoting a math operation in the currentRange assignment. That shouldn't be quoted, if I'm reading it right.

 

[gravanoc]

Set currentrange = Range("G" & "(19 + i*30)", "K" & "(42 + i*30)")

Should be:
Set currentrange = Range("G" & (19 + i*30), "K" & (42 + i*30))

 

[maabadi]

Welcome to MrExcel Message Board!
Please test this:

Sub SortPM()
Dim i As Long
Dim currentrange As Range
Dim column1range As Range
Dim column2range As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range

For i = 0 To 19

Set Rng1 = Cells(i * 30 + 19, 7)
Set Rng2 = Cells(i * 30 + 42, 7)
Set Rng3 = Cells(i * 30 + 19, 11)
Set Rng4 = Cells(i * 30 + 42, 11)

Set currentrange = Range(Rng1, Rng4)  'Does not work (range of object - 'Global' failed)
Set column1range = Range(Rng1, Rng2)
Set column2range = Range(Rng3, Rng4)

currentrange.Select

ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 192, 0)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 230, 153)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 242, 204)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(84, _
150, 53)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add Key:=Range( _
column2range), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Priority map").Sort
.SetRange currentrange
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Next i

End Sub

 

[skpper]

Thank you for the speedy assistance @maabadi and @gravanoc .

Maabadi's solution works like a charm.

Appreciate it!

----------
For other users' reference, below is the final code (there was a small typo in my column2range referencing in the previous post):

Sub SortPMX()

Dim i As Long
Dim currentrange As Range
Dim column1range As Range
Dim column2range As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range

For i = 0 To 19

Set Rng1 = Cells(i * 30 + 19, 7)
Set Rng2 = Cells(i * 30 + 42, 7)
Set Rng3 = Cells(i * 30 + 19, 11)
Set Rng4 = Cells(i * 30 + 42, 11)

Set currentrange = Range(Rng1, Rng4)
Set column1range = Range(Rng1, Rng2)
Set column2range = Range(Rng3, Rng4)

currentrange.Select

ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Clear

'sort by cell colour (4 levels)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 192, 0)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 230, 153)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255 _
, 242, 204)
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add(column1range, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(84, _
150, 53)

'sort by cell value
ActiveWorkbook.Worksheets("Priority map").Sort.SortFields.Add Key:=column2range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

With ActiveWorkbook.Worksheets("Priority map").Sort
.SetRange currentrange
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Next i

End Sub

 

[maabadi]

You 're welcome & Thanks for Feedback.