Sort variable number of rows by column

[BakerBaker]

Help greatly appreciated on this. Below is a simple representation of a larger data set. I wish to sort each block of rows by column with the numerical value in Col. B descending from Largest to Smallest.

Col. A	Col.B
Birds		Dogs
Dogs		Birds
Horses		Horses
	Blank
Cows		Pigs
Sheep		Sheep
Pigs		Cows
Goats		Goats
	Blank
Poultry		Horses
Cows		Cows
Horses		Domestic
Pigs		Others
Others		Pigs
Domestic		Poultry
		Blank
etc. Thanks

<tbody>
[TD="width: 113"]To give result:[/TD]
[TD="width: 85"]Col. A[/TD]
[TD="width: 64"]Col.B[/TD]

[TD="class: xl65"]9[/TD]

[TD="class: xl65"]14[/TD]

[TD="class: xl65"]14[/TD]

[TD="class: xl65"]9[/TD]

[TD="class: xl65"]3[/TD]

[TD="class: xl65"]3[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65"]4[/TD]

[TD="class: xl65"]16[/TD]

[TD="class: xl65"]11[/TD]

[TD="class: xl65"]11[/TD]

[TD="class: xl65"]16[/TD]

[TD="class: xl65"]4[/TD]

[TD="class: xl65"]4[/TD]

[TD="class: xl65"]4[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65"]3[/TD]

[TD="class: xl65"]34[/TD]

[TD="class: xl65"]22[/TD]

[TD="class: xl65"]22[/TD]

[TD="class: xl65"]34[/TD]

[TD="class: xl65"]14[/TD]

[TD="class: xl65"]4[/TD]

[TD="class: xl65"]11[/TD]

[TD="class: xl65"]11[/TD]

[TD="class: xl65"]4[/TD]

[TD="class: xl65"]14[/TD]

[TD="class: xl65"]3[/TD]

</tbody>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG12Feb20
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A:A").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    Dn.Resize(, 2).Sort Dn(1).Offset(, 1), xlDescending
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[BakerBaker]

Try this:-

[COLOR=Navy]Sub[/COLOR] MG12Feb20
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range("A:A").SpecialCells(xlCellTypeConstants)
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Areas
    Dn.Resize(, 2).Sort Dn(1).Offset(, 1), xlDescending
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Works a treat! Thanks for the fast reply.

 

[MickG]

You're welcome