VBA - Flexible Sort Macro

[jordanctc]

Good morning.

In Excel 2003, I know you can have three criteria of sorting (Sort by
Then by, Then by).

I am trying to create a form where I give the user the same 6 option
(representing columns D through I of the sheet) for sorting. I hav
form controls to stop sorting the same column more than once but I'
just not sure what the VBA should be for the sort.

Can I use variables and pass them to a single sort statement?

Do I need different sort statements if they want to sort by 1, 2, or
criteria?

Thanks,
Jorda

 

[Bob Phillips]

Jordan,

Try something like this

Sub Sortit(rngToSort As Range, _
rng1 As Range, Optional rng2, Optional rng3)

If IsMissing(rng2) Then Set rng2 = rng1
If IsMissing(rng2) Then Set rng3 = rng2

rngToSort.Sort key1:=rng1, _
key2:=rng2, _
key3:=rng3
End Sub

rngTosort is the whole range to be sorted, and pass the keys as rng1,2,3
omitting if required.

It will sort key2 twice or even key1 three times, but that shouldn't be a
problem.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[jordanctc]

Hey thanks for the help. Can someone look at this code - am I on th
right track or WAY off course?? I've never used a range variable an
appearantly haven't yet figured out the trick....

Private Sub findDR_Click()
Dim rngSort1 As Range, rngSort2 As Range, rngSort3 As Range

If ManualForm.o1_DR.Value Then
rngSort1 = "D2"
ElseIf ManualForm.o1_Date.Value Then
rngSort1 = "E2"
ElseIf ManualForm.o1_Trailer.Value Then
rngSort1 = "F2"
ElseIf ManualForm.o1_Company.Value Then
rngSort1 = "G2"
ElseIf ManualForm.o1_ULT.Value Then
rngSort1 = "H2"
ElseIf ManualForm.o1_Weight.Value Then
rngSort1 = "I2"
Else
MsgBox "You must select at least one catagory to be able t
sort.", vbOKOnly, "CTC"
Exit Sub
End If

If ManualForm.o2_DR.Value Then
rngSort2 = "D2"
ElseIf ManualForm.o2_Date.Value Then
rngSort2 = "E2"
ElseIf ManualForm.o2_Trailer.Value Then
rngSort2 = "F2"
ElseIf ManualForm.o2_Company.Value Then
rngSort2 = "G2"
ElseIf ManualForm.o2_ULT.Value Then
rngSort2 = "H2"
ElseIf ManualForm.o2_Weight.Value Then
rngSort2 = "I2"
Else
rngSort2 = rngSort1
End If

If ManualForm.o3_DR.Value Then
rngSort3 = "D2"
ElseIf ManualForm.o3_Date.Value Then
rngSort3 = "E2"
ElseIf ManualForm.o3_Trailer.Value Then
rngSort2 = "F2"
ElseIf ManualForm.o3_Company.Value Then
rngSort2 = "G2"
ElseIf ManualForm.o3_ULT.Value Then
rngSort2 = "H2"
ElseIf ManualForm.o3_Weight.Value Then
rngSort3 = "I2"
Else
rngSort3 = rngSort2
End If

Range("A:I").Sort key1:=rngSort1, _
key2:=rngSort2, _
key3:=rngSort3

End Su