Macro to sort multiple rows individually

[roymunoz03]

Hi,

I need to create a macro to sort multiple data ranges individually at once, but I need to use custom order by cell color.

I tried to combine one macro that I found to sort multiple rows with the one I had, but I get an error message "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort by box isn't the same or blank."

I appreciate any input to be able to run my macro.

Sub Macro1()
'
' Macro1 Macro
'

ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear

For x = 7 To 9
myrange = "J" & x & ":" & "M9"



ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add(Range(myrange), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(216, _
228, 188)
ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add(Range(myrange), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(177, _
160, 199)
ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range(myrange), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").sort
.SetRange Range(myrange)
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

Next x

End Sub
'

 

[venkat1926]

will not sorting each column separately vitiate your database. anyhow try this macro on COPY OF THE FILE AND NOR ORIGINAL FILE.
row no. 1 is having column headings.

Sub sorting()
Dim j As Integer, r As Range

For j = 1 To Range("A1").End(xlToRight).Column
Set r = Range(Cells(j, 1), Cells(j, 1).End(xlDown))
r.Sort key1:=Range("A1"), header:=xlYes
Next j
End Sub