Solved: Conditional Sorting

[Ringhal]

Hi all,

In my table, I have 6 columns (A-F). In column A I have blank cells or numbers. I need to sort this table by column B, then by C, and then by column D, but only the rows where column A has a number (more than 0) and place these rows at the top. The table Range is A4:F27.


Here is the code for the sorting part (from a macro recording):
[vba] Range("A4:F27").Select
ActiveWorkbook.Worksheets("Today").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Today").Sort.SortFields.Add Key:=Range("B4:B27") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Today").Sort.SortFields.Add Key:=Range("C4:C27") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Today").Sort.SortFields.Add Key:=Range("D427") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Today").Sort
.SetRange Range("A4:F27")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With[/vba]

[p45cal]

Record yourself a macro sorting the whole range on only column A. I think this will place rows without anything in column A at the bottom, keeping them in the order they were in.
Next, select only the top part of the range and sort that as before.
Paste the macro here and we'll tweak to automate the definition of the second range to sort.

[p45cal]

Playing with this a bit more, rty perhaps:[VBA]Sub blah()
With ActiveWorkbook.Worksheets("Today")
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A5:A27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A4:F27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set SecondRange = .Range(.Range("A4"), .Range("A4").End(xlDown)).Resize(, 6)
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("C5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("D5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange SecondRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub
[/VBA]

[Aussiebear]

[QUOTE=RinghalIn my table, I have 6 columns (A-F). In column A I have blank cells or numbers. I need to sort this table by column B, then by C, and then by column D, but only the rows where column A has a number (more than 0) and place these rows at the top. The table Range is A4:F27.[/Quote]

Since the order of operations is determined by the condition that you only sort those rows in Column A where it has a number, then Column A needs to have preference over the others. Sort by Columns A then B then C then D, rather than the logic of the recorded code in the first post.

[Ringhal]

Thanks Aussiebear and p45cal

p45cal: I used your code above and changed it a little bit to work with my data. It now function as it should.
[vba] Sub blah()

Dim SecondRange As Range
Dim i As Byte

With ActiveWorkbook.Worksheets("Today")
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A3:A27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A4:F27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

i = Worksheets("Today").Range("A3:A27").Cells.SpecialCells(xlCellTypeConstants) .Count + 1
Set SecondRange = .Range(.Range("A3"), .Range("A3").End(xlDown)).Resize(i, 6)

With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("D3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange SecondRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub
[/vba]