VBA sort sheets depending of the number of rows used

[Leske]

Hi,

I was wondering if there was an easy code to use to sort my sheets based on the used rows.

So i have several sheets over the 100 and i would like to see them from the biggest line items to the smallest line items.

Suggestions?

Kind regards,

Les

 

[MickG]

Try this:-
The code assumes that the row counts in each sheet is different !!!!

[COLOR=navy]Sub[/COLOR] MG18Jul42
[COLOR=navy]Dim[/COLOR] shtcnt [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] sht [COLOR=navy]As[/COLOR] Worksheet, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
shtcnt = Worksheets.Count
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] sht [COLOR=navy]In[/COLOR] Worksheets
    .Item(sht.UsedRange.Rows.Count) = sht.Name
[COLOR=navy]Next[/COLOR] sht
Application.ScreenUpdating = False
[COLOR=navy]For[/COLOR] n = 1 To shtcnt
      Sheets(.Item(Application.Large(.keys, n))).Move _
      after:=Sheets(Sheets.Count) 
[COLOR=navy]Next[/COLOR] n
Application.ScreenUpdating = True
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Leske]

And what does he do when some sheets have the same amount of rows?

 

[MickG]

In that case, he will have to decide how he wants his sheets sorted and ask if the code can be altered to take care of that situation !!