PDA

View Full Version : [SOLVED] Sorting Different Ranges on Different Columns



Changedsoul
08-31-2012, 01:17 PM
Hi, Im struggling with trying to sort some data. I have a spread sheet with bunch of data. Its a shipping schedule, and when something on the list gets shipped, I color the row cells yellow.

I have a macro working that sorts the entire sheet by color yellow and places the yellow cells on top. But what I am having a problem with now is isolating color groups for other sorting.

For example, Once all the yellow Cells are sorted on top, I want to sort just the yellow cells alphabetically. So I need to be able to isolate just the Yellow cells and select them for sorting.

Then I need to select everything else except the yellows and sort them by Date on a different column.

So My question is, how am I able to select groups of cells based on color?

Teeroy
08-31-2012, 03:46 PM
The following should work. Be aware that it assumes all data fields are filled, that Row1 is a header row, the sort column is Col1 and it works on the activesheet.


Sub sort_within_color()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
'work out where yellow cells end
Set rng = [A2]
Do While rng.Interior.Color = vbYellow
Set rng = rng.Offset(1, 0)
Loop
'set and sort colored range
Set rng1 = Range([A2], rng.Offset(-1).End(xlToRight))
rng1.Sort Key1:=rng1.Cells(, 1) 'change 1 to your sort col
'set and sort non-colored range
Set rng2 = Range(rng, rng.End(xlToRight).End(xlDown))
rng2.Sort Key1:=rng2.Cells(, 1) 'change 1 to your sort col
End Sub

Changedsoul
09-01-2012, 07:29 AM
Awesome, that worked great. I was able to tweak it to make do exactly what I needed.

Also, on a side note, Do you know of any good VBA documentation? The MS Help files are complete garbage. I was hoping for a great listing of Functions and member functions and examples.

Teeroy
09-01-2012, 03:42 PM
Glad it worked for you.

For a general overview try "Programming in Microsoft Excel VBA An Introduction" and in a bit more depth "Excel VBA Programming For Dummies". There's also a training and certification course on this forum if you prefer more structured learning (I don't know enough about it to recommend it); just click training on the top menu bar to have a look.

Once you build a base have a scan through the Articles and Knowledge Base on this forum. I also learn a lot from reading other answers here.

A well formed search string in Google can help with most situations and the help files for specific functions MSDN can be OK (but you need to know exactly what you're after). And don't forget if you get stuck just ask a question here; most people are quite willing to help you learn.