XLA routines: EE_SortTable2003Comp
Sort data using VBA in Excel. Works with Excel 2003, and takes header names as strings.
Sub EE_SortTable2003Comp(eeTable As range, SortCol1 As String, Optional SortCol1Asc As Boolean = True, Optional SortCol2 As String, Optional SortCol2Asc As Boolean = True, Optional SortCol3 As String, Optional SortCol3Asc As Boolean = True) '- Uses Excel 2003 compatible sorting Dim intCol1 As Integer Dim intCol2 As Integer Dim intCol3 As Integer Dim intOrder1 As Integer Dim intOrder2 As Integer Dim intOrder3 As Integer 'http://excelexperts.com/xla-routines-EESortTable2003Comp If SortCol1 <> "" Then intCol1 = Application.WorksheetFunction.Match(SortCol1, eeTable.Rows(1), 0) intOrder1 = IIf(SortCol1Asc = True, xlAscending, xlDescending) End If If SortCol2 <> "" Then intCol2 = Application.WorksheetFunction.Match(SortCol2, eeTable.Rows(1), 0) intOrder2 = IIf(SortCol2Asc = True, xlAscending, xlDescending) End If If SortCol3 <> "" Then intCol3 = Application.WorksheetFunction.Match(SortCol3, eeTable.Rows(1), 0) intOrder3 = IIf(SortCol3Asc = True, xlAscending, xlDescending) End If With eeTable If SortCol1 <> "" And SortCol2 = "" And SortCol3 = "" Then .Sort Key1:=.Cells(2, intCol1), Order1:=intOrder1, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal ElseIf SortCol1 <> "" And SortCol2 <> "" And SortCol3 = "" Then .Sort Key1:=.Cells(2, intCol1), Order1:=intOrder1, Key2:=.Cells(2, intCol2), _ Order2:=intOrder2, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal ElseIf SortCol1 <> "" And SortCol2 <> "" And SortCol3 <> "" Then .Sort Key1:=.Cells(2, intCol1), Order1:=intOrder1, Key2:=.Cells(2, intCol2), _ Order2:=intOrder2, Key3:=.Cells(2, intCol3), Order3:=intOrder3, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal End If End With End Sub
»
- Nick's blog
- Login or register to post comments
- 3085 reads
Recent comments
5 years 41 weeks ago
6 years 27 weeks ago
6 years 39 weeks ago
6 years 42 weeks ago
6 years 43 weeks ago
6 years 48 weeks ago
7 years 4 weeks ago
7 years 5 weeks ago
7 years 5 weeks ago
7 years 5 weeks ago