View Full Version : [SOLVED] Select and sort rows based on multiple criteria

03-14-2016, 01:39 PM
I have a script that sorts my workbook by a certain set of criteria (call it MM-YY, column S), then by additional criteria first in Column R (value is 6 or 7), then by column O (a date). Once this sort is accomplished, I need to further sort all the rows where the column S value = this month (i.e. MM-YY) and the column R criteria = 7, by the values in columns F then V. I can manage the sort sequences easily enough; where I am completely stumped is how to "select" the rows where the columns S value matches the current month and the column R criteria is 7, from which to facilitate the sort.

Can anyone offer some guidance or a starter script I tinker with? I've been trying all kinds of variations of stuff I Google to no avail, and really appreciate any help anyone is willing to provide. My next stop is my VBA guru on Fiverr.com if nobody here has a solution for me.


03-16-2016, 08:01 AM
I was able to resolve my issues as follows; persistence pays off!

Dim nRow As Long
Dim nStart As Long, nEnd As Long

'Calculate current PMS month string
strDate = Format(Date, "MMDDYY")
If Left(strDate, 2) < 10 Then
strPMSMonth = Mid(strDate, 2, 1)
strPMSMonth = Trim(Left(strDate, 2))
End If

strPMSYear = Right(strDate, 2)
strPMSDate = strPMSMonth & "-" & strPMSYear
'MsgBox strPMSDate

' Figure out where CURRENT TYPE 7 file data starts.
For nRow = 1 To 65536
If Range("S" & nRow).Value = strPMSDate And Range("R" & nRow).Value = 7 Then
nStart = nRow
Exit For
End If
Next nRow
On Error Resume Next

' Figure out where the CURRENT TYPE 7 file data ends.
For nRow = nStart To 65536
If Range("R" & nRow).Value <> 7 Then
nEnd = nRow
Exit For
End If
Next nRow
nEnd = nEnd - 1

Range("A" & nStart - 1 & ":AD" & nEnd).Select 'Added -1 to nStart because top row always sorts out of sequence otherwise

With ActiveSheet.Sort
'the key you want to use is the column to sort on. column 1 is "A", column "B" is 2, etc
.SortFields.Add Key:=Selection.Columns(6), Order:=xlAscending
.SortFields.Add Key:=Selection.Columns(22), Order:=xlAscending
.SetRange Selection

End With