Solved: Sorting rows between rows

[austenr]

I need to sort rows between a value in column E in Descending order. The sort key should be column E. Basically, all rows between the value EE. For example,
if you have EE in row 5 column E, sort the rows between that row and the next row containing EE in descending order. any ideas?

[matthewspatrick]

Something like this should work (it did in my quickie testing):

Sub SpecialSort()
Dim FirstOne As Long
Dim SecOne As Long

Const LookForStr As String = "EE"

On Error Resume Next

FirstOne = [e:e].Find(What:=LookForStr, After:=[e65536], MatchCase:=False).Row + 1
If Err <> 0 Then
Err.Clear
MsgBox "Search string not found", vbCritical, "Aborting"
Exit Sub
End If

SecOne = Range(Cells(FirstOne + 1, 5), Cells(65536, 5)).Find(What:=LookForStr, After:=[e65536], _
MatchCase:=False).Row - 1
If Err <> 0 Then
Err.Clear
SecOne = [e65536].End(xlUp).Row
End If

On Error GoTo 0

Range(Cells(FirstOne, 5), Cells(SecOne, 5)).EntireRow.Sort Key1:=Cells(FirstOne, 5), _
Order1:=xlAscending, Header:=xlNo

End Sub

[lior03]

hello
could you guys provide an example.i could not get it.
thanks

[austenr]

Thnks Matt. Works great!! http://vbaexpress.com/forum/images/smilies/023.gif Solved

[austenr]

I thought that this was working but there is still a problem. Here is what is not working. If you have the following:

EE
CH
CH
CH
CH
CH
CH
CH
SP

at the end of your sort data it is not sorted. However if this is the only thing in your sort it sorts correctly. What gives? :dunno

[austenr]

Here is a sample file.

[matthewspatrick]

Austen,

The sub is designed to do the following:

Look for the first instance of EE, and capture the row number after that first instance (FirstOne); if no instance is found, quit
Look for the next instance of EE. If found, capture the row number before that instance (SecOne). If not found, capture the row number of the last populated cell in that column
Sort Rows FirstOne through SecOne

The sub does that quite well :devil:

I had interpreted your question such that you only needed to find one interval marked by 'EE'. Do you in fact need every interval marked by 'EE'?

Patrick

[austenr]

Hi,

Yes, what I need to do is sort all rows between every instance of EE. Sorry for the confusion. :doh:

[matthewspatrick]

Not the most elegant piece of code ever, but it appears to work:

Option Explicit
Sub SpecialSort()

Dim FirstOne As Long 'first row in block to sort
Dim SecOne As Long 'second row in block to sort
Dim FirstLook As Boolean 'is first pass at the data
Dim rng As Range 'marker used to help set find range
Dim EndIt As Boolean 'toggle to end the program

Const LookForStr As String = "EE" 'text sought for

EndIt = False
FirstLook = True
Set rng = [e65536]

On Error GoTo ErrHandler

Do Until EndIt
If Not FirstLook Then
FirstOne = Range(rng, Cells(65536, 5)).Find(What:=LookForStr, After:=[e65536], _
MatchCase:=False).Row + 1
Else
FirstOne = [e:e].Find(What:=LookForStr, After:=rng, MatchCase:=False).Row + 1
FirstLook = False
End If
SecOne = Range(Cells(FirstOne + 1, 5), Cells(65536, 5)).Find(What:=LookForStr, _
After:=[e65536], MatchCase:=False).Row - 1
Set rng = Cells(SecOne + 1, 5)
DoTheSort:
If FirstOne < SecOne Then
Range(Cells(FirstOne, 5), Cells(SecOne, 5)).EntireRow.Sort Key1:=Cells(FirstOne, 5), _
Order1:=xlDescending, Header:=xlNo
End If
Loop

Exit Sub

ErrHandler:
If FirstLook Then
MsgBox "Search string not found", vbCritical, "Aborting"
ElseIf FirstOne > 0 Then
SecOne = [e65536].End(xlUp).Row
EndIt = True
Resume DoTheSort
End If

End Sub



Patrick

[austenr]

Thanks Matthew. That worked. Nice job :thumb

[austenr]

I just discovered yet another problem. Can I add a second sort to the routine above? I need to sort secondary on column B.

[matthewspatrick]

Just modify the line with the Sort to include Key2 and Order2 arguments. (You are allowed up to three sort keys.)

[austenr]

ok i will give it a go and let you know how it turns out. Thanks