Macro to sort ranges in column

[mikey1987]

Hello all, I'm looking for a macro to sort ranges based on a recurring word in column W. In column W I have a word ("Wave 4") and every time I see that word, I want to sort the range based on the values in column W from big to small. And then it has to look for the next Wave 4 word and do it all over again. Allow me to visualise below. You see that"Wave 4" is in cell W6, W19 and W 32. So I want to sort range T6 to W13 based on the values in column W (already done here). Then I want the macro to look at the next "Wave 4" and do the same again. Is this possible?

This would be a tremendous help, thanks in advance.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG09Dec08
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("w1"), Range("w" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 21)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]If[/COLOR] Dn(1).Offset(-1) = "Wave 4" [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, -3).Resize(, 4).Sort Range(Split(Dn.Address, ":")(0)), xlDescending
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mikey1987]

Hello Mick,
Thanks for helping, but your macro doesn't seem to do anything. I don't know enough about macros to correct them though...
Any other ways to get to it?

Kind regards

 

[MickG]

Have a look at this sample file:-
https://app.box.com/s/ahn6bpqpp7viuurpzfho

Regrds Mick

 

[mikey1987]

Have a look at this sample file:-
https://app.box.com/s/ahn6bpqpp7viuurpzfho

Regrds Mick

Hello,

Well, it does seem that your macro does what I want, but for some reason it doesn't do it for me
The only 2 reasons I can come up with is:
- the rows between the different tables are variable. In your example there are always 4 rows in between them
- the number of data rows are also variable, you always have 8. I have data for any number of companies (well, between 2 and 10 at least)

That's why the record macro function is of absolutely no use to us

I know it's a very hard thing to accomplish (might even be impossible, at least my colleague couldn't find a way), but it would save us ages

 

[MickG]

The spacing of the sets of data should make no difference, It may be your "Wave 4" has extra spaces or something else. Try adding your data to my headers for each set of data and re run the code.
If your data matches the Criteria of the code it will work.
If your running the code from an ordinary Module you could try changing the line below to include "Activesheet "

With ActiveSheet
    Set Rng = .Range(.Range("w1"), .Range("w" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 21)
End With
end with

Regrds Mick

 

[mikey1987]

It works!!!!
Thanks man, you're the best.
Turned out the problem was, in addition to what you said, that the word was actually "wave 4" instead of "Wave 4".
Man I'm gonna score with the boss now

 

[MickG]

Thank for the FeedBack , I'm Please you've got it working.
Regrds Mick

 

[mikey1987]

Hello ,
Turns out a little detail is missing, my companies don't sort along. The sort has to be expanded to the 4 columns to the left of it.
Hope this picture shows what I mean

Any ideas?

 

[MickG]

Try this:-
It requires that column "S" with the letters is there as marker for each set of data.
The real problem is the Blank spaces within the data.!!

[COLOR=Navy]Sub[/COLOR] MG11Dec17
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]With[/COLOR] ActiveSheet
    [COLOR=Navy]Set[/COLOR] Rng = .Range(.Range("S1"), .Range("S" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Areas
   [COLOR=Navy]If[/COLOR] Dn(1).Offset(-1, 4) = "Wave 4" [COLOR=Navy]Then[/COLOR]
      [COLOR=Navy]With[/COLOR] .Sort
        .SortFields.Add Key:=Dn.Offset(, 4), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Dn.Offset(, 1).Resize(, 4) 
        .Apply
     [COLOR=Navy]End[/COLOR] With
   [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick