Insert Blank Line and Sort

  • Thread starter Thread starter Saxman
  • Start date Start date
S

Saxman

Within a worksheet I have an example of data below consisting of four
columns.

I need to put a blank line in between the Racecourse (where they change)
and after do a sort on Rating (high to low).

Is it possible?

I could do it with a macro, but am unable to fathom out how to deal with a
differing number of horses at each racecourse.

Time Racecourse Horse Rating
04:30:00 SANDOWN Dare To Dance 0.261
04:30:00 SANDOWN Garud 0.153
04:30:00 SANDOWN West Brit 0.073
04:30:00 SANDOWN Rockerfellow 0.073
04:30:00 SANDOWN Hope Point 0.073
04:30:00 SANDOWN Nuba 0.073
04:30:00 SANDOWN Eclipseoftheheart 0.073
04:40:00 UTTOXETER Red Current 0.081
04:40:00 UTTOXETER Bull Market 0.074
04:40:00 UTTOXETER Thetasteofparadise 0.069
04:40:00 UTTOXETER Claude Carter 0.046
04:40:00 UTTOXETER Hollies Favourite 0.046
04:40:00 UTTOXETER A Stones Throw 0.02
04:50:00 BATH What Katie Did 0.038
04:50:00 BATH Too Many Questions 0.075
04:50:00 BATH My Meteor 0.089
04:50:00 BATH Miss Firefly 0.091
04:50:00 BATH Katmai River 0.093
04:50:00 BATH Flaxen Lake 0.086
04:50:00 BATH Crimson Queen 0.08
04:50:00 BATH Bold Argument 0.075
04:50:00 BATH Avonlini 0.043
05:00:00 SANDOWN Billion Dollar Kid 0.058
05:00:00 SANDOWN Formal Demand 0.222
05:00:00 SANDOWN Jungle Bay 0.101
05:00:00 SANDOWN Junket 0.058
05:00:00 SANDOWN Kingarrick 0.006
05:00:00 SANDOWN Mary's Pet 0.063
05:00:00 SANDOWN Miss Bootylishes 0.148
05:00:00 SANDOWN Push Me 0.065
05:00:00 SANDOWN Tuxedo 0.084
05:00:00 SANDOWN Whitechapel 0.21
 
Within a worksheet I have an example of data below consisting of four
columns.

I need to put a blank line in between the Racecourse (where they change)
and after do a sort on Rating (high to low).

Is it possible?

I could do it with a macro, but am unable to fathom out how to deal with
a
differing number of horses at each racecourse.

Time Racecourse Horse Rating
04:30:00 SANDOWN Dare To Dance 0.261
04:30:00 SANDOWN Garud 0.153
04:30:00 SANDOWN West Brit 0.073
04:30:00 SANDOWN Rockerfellow 0.073
04:30:00 SANDOWN Hope Point 0.073
04:30:00 SANDOWN Nuba 0.073
04:30:00 SANDOWN Eclipseoftheheart 0.073
04:40:00 UTTOXETER Red Current 0.081
04:40:00 UTTOXETER Bull Market 0.074
04:40:00 UTTOXETER Thetasteofparadise 0.069
04:40:00 UTTOXETER Claude Carter 0.046
04:40:00 UTTOXETER Hollies Favourite 0.046
04:40:00 UTTOXETER A Stones Throw 0.02
04:50:00 BATH What Katie Did 0.038
04:50:00 BATH Too Many Questions 0.075
04:50:00 BATH My Meteor 0.089
04:50:00 BATH Miss Firefly 0.091
04:50:00 BATH Katmai River 0.093
04:50:00 BATH Flaxen Lake 0.086
04:50:00 BATH Crimson Queen 0.08
04:50:00 BATH Bold Argument 0.075
04:50:00 BATH Avonlini 0.043
05:00:00 SANDOWN Billion Dollar Kid 0.058
05:00:00 SANDOWN Formal Demand 0.222
05:00:00 SANDOWN Jungle Bay 0.101
05:00:00 SANDOWN Junket 0.058
05:00:00 SANDOWN Kingarrick 0.006
05:00:00 SANDOWN Mary's Pet 0.063
05:00:00 SANDOWN Miss Bootylishes 0.148
05:00:00 SANDOWN Push Me 0.065
05:00:00 SANDOWN Tuxedo 0.084
05:00:00 SANDOWN Whitechapel 0.21
Click to expand...


///////////////////////////////////////////////////////////////////

I found this bit of code from the OzGrid site which inserts a blank row
where there is a change of data.

Sub InsertDividers()
'
' on active sheet, move down thru column A
' If current cell Is Not the same As previous
' insert a row.
' stop when current testing cell In A:A Is empty
'
Dim lngRow As Long

lngRow = 3
Do While Cells(lngRow, 1) <> ""
If Cells(lngRow, 1) <> Cells(lngRow - 1, 1) Then
' change In NAME value so insert row
Rows(lngRow).Insert
lngRow = lngRow + 1
End If
lngRow = lngRow + 1
Loop

End Sub

All I need is to sort each group of data in column 4 high to low.
 
hi,

Sub Macro1()
rws = Range("B65536").End(xlUp).Row
For i = Range("B65536").End(xlUp).Row To 2 Step -1
If Range("B" & i - 1) <> Range("B" & i) Then
Rows(i & ":" & rws).Sort Key1:=Range("D" & i), Order1:=xlDescending, Header:=xlNo
Rows(i).Insert Shift:=xlDown
rws = i - 1
End If
Next
End Sub
 
hi,

Sub Macro1()
rws = Range("B65536").End(xlUp).Row
For i = Range("B65536").End(xlUp).Row To 2 Step -1
If Range("B" & i - 1) <> Range("B" & i) Then
Rows(i & ":" & rws).Sort Key1:=Range("D" & i), Order1:=xlDescending,
Header:=xlNo
Rows(i).Insert Shift:=xlDown
rws = i - 1
End If
Next
End Sub
Click to expand...

Thanks isabelle. Had a quick go at this and it certainly sorts. I just
need to amend the ranges to 'D' I think? Off to a beer festival now.
Will have another go Saturday.
 
Back
Top