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	To give result:	Col. A	Col.B
Birds	9		Dogs	14
Dogs	14		Birds	9
Horses	3		Horses	3
		Blank
Cows	4		Pigs	16
Sheep	11		Sheep	11
Pigs	16		Cows	4
Goats	4		Goats	4
		Blank
Poultry	3		Horses	34
Cows	22		Cows	22
Horses	34		Domestic	14
Pigs	4		Others	11
Others	11		Pigs	4
Domestic	14		Poultry	3
		Blank
etc. Thanks

<tbody>
</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