The code below sorts on column I, but you can change it to any column, or add
multiple sort keys. I used the file on the website to test the code.
Sub sortranges()
Lastrow = Cells(Rows.Count, "E").End(xlUp).Row
RowCount = 1
findProduct = False
Do While RowCount <= Lastrow
If findProduct = False Then
If Not IsEmpty(Cells(RowCount, "E")) Then
Product = Cells(RowCount, "E")
StartRange = RowCount
findProduct = True
End If
Else
If IsEmpty(Cells(RowCount + 1, "E")) Or _
Cells(RowCount + 1, "E") < Product Then
Set SortRange = Range(Cells(StartRange, "A"), _
Cells(RowCount, "J")) _
SortRange.Sort _
Key1:=Range("I" & StartRange), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
findProduct = False
End If
End If
RowCount = RowCount + 1
Loop
End Sub
" wrote:
Afternoon, Can anyone code the solution or point me in the right
direction please? I have files that look like the one on the link
(http://www.inasentimentalmood.co.uk/Book1.xls). I need to go through
to the bottom of the document(or last row in column E) and sort all
the sections. So it would start and sort row 10-11, then 13-19, then
22-25 etc. I can highlight all the rows using...
Set myRg = Range([e10], [e65536].End(xlUp))
Set myRg = myRg.SpecialCells(xlCellTypeConstants).EntireRow.S elect
....but I don't think this is a the way to do it. I guess I can use
xlDown working through column F and then select the rows which I've
passed but I don't know how to code it. Any ideas? I would be so
greatful for any help!