Looping and sorting through blocks of rows

[zeekmcphee]

Hello
I am looking for a VBA solution
I have a sheet with several thousand rows.The rows are split up into blocks of rows and seperated by an empty row.Each block of rows will vary with entries so these are random

The sheet has 7 fields author,book_ title,publisher,year_published,genre,number,copies_sold.
The number field is a number against each book title within the block of rows

The last field..copies_sold is a numeric field and what I would like to do is sort each authors copies sold field from least sold to most sold.
If the sheet was one long continuous record then sorting would be easy,but it is a series of blocks of records for each author.
Is there a way that I could some how loop through each individual authors block and sort the copies sold field from smallest to largest;
Below is an example of just three authors,there are three blocks of rows of 4,6and7

[TABLE="width: 100"]
<tbody>[TR]
[TD]author_name.. [/TD]
[TD]book_title..[/TD]
[TD]publisher..[/TD]
[TD]year_published[/TD]
[TD]number[/TD]
[TD]genre[/TD]
[TD]copies_sold[/TD]
[/TR]
[TR]
[TD]molly brown[/TD]
[TD]swan[/TD]
[TD]dolphin[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]mystery[/TD]
[TD]26000[/TD]
[/TR]
[TR]
[TD]molly brown[/TD]
[TD]the rock[/TD]
[TD]dolphin[/TD]
[TD]2013[/TD]
[TD]2[/TD]
[TD]mystery[/TD]
[TD]18500[/TD]
[/TR]
[TR]
[TD]molly brown[/TD]
[TD]daytime[/TD]
[TD]dolphin[/TD]
[TD]2012[/TD]
[TD]3[/TD]
[TD]thriller[/TD]
[TD]670500[/TD]
[/TR]
[TR]
[TD]molly brown[/TD]
[TD]lost[/TD]
[TD]gracey[/TD]
[TD]2006[/TD]
[TD]4[/TD]
[TD]mystery[/TD]
[TD]78000[/TD]
[/TR]
[TR]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty [/TD]
[TD]empty [/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]sunshine[/TD]
[TD]gracey[/TD]
[TD]2008[/TD]
[TD]1[/TD]
[TD]crime[/TD]
[TD]136000[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]rocket[/TD]
[TD]gracey[/TD]
[TD]2006[/TD]
[TD]2[/TD]
[TD]crime[/TD]
[TD]236000[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]last one in[/TD]
[TD]gracey[/TD]
[TD]2007[/TD]
[TD]3[/TD]
[TD]mystery[/TD]
[TD]900000[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]waterfall[/TD]
[TD]bell[/TD]
[TD]2010[/TD]
[TD]4[/TD]
[TD]mystery[/TD]
[TD]155000[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]the ring[/TD]
[TD]bell[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]thriller[/TD]
[TD]257893[/TD]
[/TR]
[TR]
[TD]ken davies[/TD]
[TD]april[/TD]
[TD]bell[/TD]
[TD]2017[/TD]
[TD]6[/TD]
[TD]thriller[/TD]
[TD]823456[/TD]
[/TR]
[TR]
[TD]empty[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]mrs jones[/TD]
[TD]bell [/TD]
[TD]2017[/TD]
[TD]1[/TD]
[TD]bio[/TD]
[TD]289000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]the parade[/TD]
[TD]gracey[/TD]
[TD]2017[/TD]
[TD]2[/TD]
[TD]bio[/TD]
[TD]456000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]carnival[/TD]
[TD]gracey[/TD]
[TD]2014[/TD]
[TD]3[/TD]
[TD]thriller[/TD]
[TD]234000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]valley[/TD]
[TD]dolphin[/TD]
[TD]2012[/TD]
[TD]4[/TD]
[TD]mystery[/TD]
[TD]980000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]my tree[/TD]
[TD]dolphin[/TD]
[TD]2010[/TD]
[TD]5[/TD]
[TD]romance[/TD]
[TD]345000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]talk[/TD]
[TD]dolphin[/TD]
[TD]2009[/TD]
[TD]6[/TD]
[TD]romance[/TD]
[TD]650000[/TD]
[/TR]
[TR]
[TD]dawn ellis[/TD]
[TD]smile[/TD]
[TD]bell[/TD]
[TD]2003[/TD]
[TD]7[/TD]
[TD]thriller[/TD]
[TD]345000[/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes sense
ZM

 

[Dryver14]

Use this on a copy first.

You will need to remove the headings from row one to start with so the first Author starts in row 2 Just copy to one side..but not connected to column 7

It assumes you are on the activesheet.

Sub KWSorted()


Dim i As Long
Dim LastRow As Long


LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To LastRow


    If Cells(i, 1) = "" Then i = i + 1
    If Cells(i - 1, 1) <> "" Then
        i = i + 1
    Else
        ActiveSheet.Cells(i, 1).CurrentRegion.Select
        Selection.Sort key1:=Cells(i, 7), order1:=xlAscending
    End If


Next i


End Sub

 

[hiker95]

zeekmcphee,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider, that is based on your flat text display, that will run in the active worksheet.

Please test this first on a copy of your original workbook/worksheet.

Sub zeekmcphee()
' hiker95, 02/27/2018, ME1045426
Application.ScreenUpdating = False
Dim Area As Range, sr As Long, er As Long
For Each Area In Range("A2", Range("G" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    Range("A" & sr & ":G" & er).Sort key1:=Range("G" & sr), order1:=1
  End With
Next Area
Application.ScreenUpdating = True
End Sub

 

[zeekmcphee]

Hello Hiker95,
Many many thanks for that code it works perfectly.Could I possibly ask you another question.I have another spreadsheet that works in a similar way to this one i.e the records are split up into groups.,and it documents students maths marks over a given time.Here is an example.What I would like to do is add another column(perhaps bewteen week and score columns)
that gives the position of the student in that record

[TABLE="width: 200"]
<tbody>[TR]
[TD]name[/TD]
[TD]year[/TD]
[TD]level[/TD]
[TD]week[/TD]
[TD]score[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]11[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]13[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[/TR]
[TR]
[TD]john ellis [/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]13[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]13[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]14[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]15[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]17[/TD]
[TD]61[/TD]
[/TR]
</tbody>[/TABLE]


to this
[TABLE="width: 200"]
<tbody>[TR]
[TD]name[/TD]
[TD]year[/TD]
[TD]level[/TD]
[TD]week[/TD]
[TD]position[/TD]
[TD]score[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]11[/TD]
[TD]1[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]jill evans[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]13[/TD]
[TD]4[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]empty[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]john ellis [/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]3[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]john ellis[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD]4[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[TD]empty
[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]15[/TD]
[TD]3[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD]4[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]17[/TD]
[TD]4[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]phil simm[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]89[/TD]
[/TR]
</tbody>[/TABLE]


So the position column reflects the new position of the score after the sort.
If two scores are the same then that will be a joint position,as with John Ellis joint 4th with tow sets of 54 and phil simm who has a joint 1st and 4th.
Hope this nakes sense.Any help much appreciated
regardsZM























j

 

[Dryver14]

I'll leave this one to you Hiker,

 

[mikerickson]

Try this to see if it gets everything

Dim oneArea as Range

For Each oneArea in Range("A:G").SpecialCells(xlCellTypeConstants).Areas
    MsgBox oneArea.Address
Next oneArea

 

[zeekmcphee]

Hello Dryver 14,
Many thanks for your reply.When I run the code it breakes down and when I debug it the following line is highlighted in yellow:
Selection.Sort key1:=Cells(i, 7), order1:=xlAscending
Does this help at all
Kind regards and thankyou for your reply

 

[Dryver14]

Thanks for the feedback, I am still learning and get told again and again to use Columns where they are fixed it should have been Selection.Sort key1:=Cells("G" & i), order1:=xlAscending

7 was column H

 

[Fluff]

@Dryver14
I think that should be

Cells(i,"G")
or
Range("G"&i)

 

[Dryver14]

Yeah, I am getting muddled here cos I tested my code and it worked, I am not sure how we have ended up on G when the original request had the amounts in column H..or column 7