Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort range by color
Hey everyone!
I need to sort a range of cells (A6:H600) by interior color. I've found some code that will sort the entire page, but I need to sort just the range. There are only cells with a color index of 6 (yellow) and all the rest are white. So all I need to do is have the yellow cells show up at the top of the range, and then I'll delete the rest. This is what I found that works for the whole worksheet: Sub MySortingMacro() 'Based on Bernard Rey's routine Const ISHEADER As Long = xlNo ' or xlYes or xlGuess Dim cell As Range Dim oldCalc As Long With Application oldCalc = .Calculation .Calculation = xlManual .ScreenUpdating = False End With With Selection(1) 'I changed this line to: With Range("D7") With .EntireColumn Columns(.Column).Insert ' Adding a temporary column For Each cell In Intersect(.Cells, ActiveSheet.UsedRange) cell.Offset(0, -1).Value = cell.Interior.ColorIndex Next cell End With .Sort Key1:=.Offset(0, -1), Order1:=xlAscending, _ header:=ISHEADER .Offset(0, -1).EntireColumn.Delete End With With Application .Calculation = oldCalc .ScreenUpdating = True End With End Sub I've seen lots of other topics on this, but nothing that will work with a specific range. Do I just need to change the line 'With Range("D7")' to something else? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort range by color
You didn't like the first suggestion??
geetarista wrote: Hey everyone! I need to sort a range of cells (A6:H600) by interior color. I've found some code that will sort the entire page, but I need to sort just the range. There are only cells with a color index of 6 (yellow) and all the rest are white. So all I need to do is have the yellow cells show up at the top of the range, and then I'll delete the rest. This is what I found that works for the whole worksheet: Sub MySortingMacro() 'Based on Bernard Rey's routine Const ISHEADER As Long = xlNo ' or xlYes or xlGuess Dim cell As Range Dim oldCalc As Long With Application oldCalc = .Calculation .Calculation = xlManual .ScreenUpdating = False End With With Selection(1) 'I changed this line to: With Range("D7") With .EntireColumn Columns(.Column).Insert ' Adding a temporary column For Each cell In Intersect(.Cells, ActiveSheet.UsedRange) cell.Offset(0, -1).Value = cell.Interior.ColorIndex Next cell End With .Sort Key1:=.Offset(0, -1), Order1:=xlAscending, _ header:=ISHEADER .Offset(0, -1).EntireColumn.Delete End With With Application .Calculation = oldCalc .ScreenUpdating = True End With End Sub I've seen lots of other topics on this, but nothing that will work with a specific range. Do I just need to change the line 'With Range("D7")' to something else? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort range by color
I'm sorry about that--for some reason it reposted my original post and
didn't post my "thank you" reply. Anyways, your suggestion actually worked perfectly for what I was trying to do. Thank you!!! On Sep 28, 3:57 pm, Dave Peterson wrote: You didn't like the first suggestion?? geetarista wrote: Hey everyone! I need to sort a range of cells (A6:H600) by interior color. I've found some code that will sort the entire page, but I need to sort just the range. There are only cells with a color index of 6 (yellow) and all the rest are white. So all I need to do is have the yellow cells show up at the top of the range, and then I'll delete the rest. This is what I found that works for the whole worksheet: Sub MySortingMacro() 'Based on Bernard Rey's routine Const ISHEADER As Long = xlNo ' or xlYes or xlGuess Dim cell As Range Dim oldCalc As Long With Application oldCalc = .Calculation .Calculation = xlManual .ScreenUpdating = False End With With Selection(1) 'I changed this line to: With Range("D7") With .EntireColumn Columns(.Column).Insert ' Adding a temporary column For Each cell In Intersect(.Cells, ActiveSheet.UsedRange) cell.Offset(0, -1).Value = cell.Interior.ColorIndex Next cell End With .Sort Key1:=.Offset(0, -1), Order1:=xlAscending, _ header:=ISHEADER .Offset(0, -1).EntireColumn.Delete End With With Application .Calculation = oldCalc .ScreenUpdating = True End With End Sub I've seen lots of other topics on this, but nothing that will work with a specific range. Do I just need to change the line 'With Range("D7")' to something else?-- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort range by color | Excel Discussion (Misc queries) | |||
HOW DO YOU SPECIFY A RANGE TO SORT? | Excel Worksheet Functions | |||
Changing the range of a table sort | New Users to Excel | |||
Sort or sub-total by Fill color or font color | Excel Discussion (Misc queries) | |||
Setting a default sort for a range | New Users to Excel |