Macro to sort multiple rows individually

R

roymunoz03

New Member
Joined
Aug 1, 2010
Messages
8
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
'
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
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.


Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Search a range for a specific color of rows, relocate those cells to another sheet, return previous sheet formatting to blank and sort secondary sheet
Replies
4
Views
371
Royalbloodi
R
R
  • Solved
Retype Macro to run properly
2
Replies
11
Views
138
Alex Blakenburg
A
Dazzawm
  • Question Question
Change Recorded Range To Used Range.
2
Replies
12
Views
935
Dazzawm
Dazzawm
E
  • Solved
Run the same Macro on All WorkSheets
Replies
3
Views
492
CephasOz
CephasOz
L
  • Question Question
Sort Excel worksheet with multiple colors
Replies
1
Views
449
kevin9999
K

Forum statistics

Threads
1,224,616
Messages
6,179,912
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top