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!

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!

Thanks Joel for replying, your code works great! I did, however; have
some success myself and plodding through like mad I came up with the
approach shown below. I'm going to test both methods on actual data
and see which comes out faster. I'll post a reply when I have the
results. Thanks again for spending your time Joel. I think your
approach might be more elegant.

Sub HideBlankRowsInCol()
Application.ScreenUpdating = False
Range("F5").Activate
While ActiveCell.End(xlDown).Row < Range("e65536").End(xlUp).Row
If ActiveCell.Offset(1, 0) = "Total" Then
ActiveCell.Offset(1, 0).Activate
Else
Set myRg = ActiveCell
Range(ActiveCell.Offset(1, 0),
ActiveCell.End(xlDown).Offset(-1, 0)).EntireRow.Select
Selection.Sort Key1:=Range("G10"),
Order1:=xlDescending
myRg.Activate
ActiveCell.End(xlDown).Activate
End If
Wend
Range(ActiveCell.Offset(1, 0),
Range("e65536").End(xlUp).Offset(0, 1)).EntireRow.Select
Selection.Sort Key1:=Range("G10"), Order1:=xlDescending
Set myRg = Range([e7], [e65536].End(xlUp))
Set myRg = myRg.Offset(0, 2).SpecialCells(xlCellTypeBlanks)
myRg.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

My Master's in Computer Science, and my Structure Programming courses
certainly help. I also had two teachers (they happen to be brothers) one
teaching assembly language and the other teaching Computer Science 101. They
were both bitches about using good programming techniques.

" wrote:

Thanks Joel for replying, your code works great! I did, however; have
some success myself and plodding through like mad I came up with the
approach shown below. I'm going to test both methods on actual data
and see which comes out faster. I'll post a reply when I have the
results. Thanks again for spending your time Joel. I think your
approach might be more elegant.

Sub HideBlankRowsInCol()
Application.ScreenUpdating = False
Range("F5").Activate
While ActiveCell.End(xlDown).Row < Range("e65536").End(xlUp).Row
If ActiveCell.Offset(1, 0) = "Total" Then
ActiveCell.Offset(1, 0).Activate
Else
Set myRg = ActiveCell
Range(ActiveCell.Offset(1, 0),
ActiveCell.End(xlDown).Offset(-1, 0)).EntireRow.Select
Selection.Sort Key1:=Range("G10"),
Order1:=xlDescending
myRg.Activate
ActiveCell.End(xlDown).Activate
End If
Wend
Range(ActiveCell.Offset(1, 0),
Range("e65536").End(xlUp).Offset(0, 1)).EntireRow.Select
Selection.Sort Key1:=Range("G10"), Order1:=xlDescending
Set myRg = Range([e7], [e65536].End(xlUp))
Set myRg = myRg.Offset(0, 2).SpecialCells(xlCellTypeBlanks)
myRg.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub