Macro to sort ranges in column

mikey1987

Board Regular
Joined
Mar 22, 2013
Messages
65
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?
1503962_589577097757313_1145816401_n.jpg
This would be a tremendous help, thanks in advance.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[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

Board Regular
Joined
Mar 22, 2013
Messages
65
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
 

mikey1987

Board Regular
Joined
Mar 22, 2013
Messages
65

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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 "
Code:
With ActiveSheet
    Set Rng = .Range(.Range("w1"), .Range("w" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 21)
End With
end with
Regrds Mick
 

mikey1987

Board Regular
Joined
Mar 22, 2013
Messages
65

ADVERTISEMENT

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 :ROFLMAO:
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Thank for the FeedBack , I'm Please you've got it working.
Regrds Mick
 

mikey1987

Board Regular
Joined
Mar 22, 2013
Messages
65
Hello :p,
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
1463568_590443714337318_425708106_n.jpg


Any ideas?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.!!
Code:
[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
 

Forum statistics

Threads
1,136,435
Messages
5,675,849
Members
419,587
Latest member
leronardo092004

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
Top