I am trying to run a macro that
a) sorts numerically by column E and G
b) and then sorts by column O
I want to keep the first 2 rowa there all the time, and it has the headers in row 3
The actual data I want to sort start by row4
The one I wrote works just fine... I made it go all the way to W2000, but I would prefer it if it can adapt to whatever data instead of a random large value.
Sub Sort_E_Gt() ' ' ActiveWorkbook.Worksheets("TEST1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("TEST1").Sort.SortFields.Add Key:=Range("E:E") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers ActiveWorkbook.Worksheets("TEST1").Sort.SortFields.Add Key:=Range("G:G") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers With ActiveWorkbook.Worksheets("TEST1").Sort .SetRange Range("A3:W2000") '>> the part I wanna change so it doesn't have a range limit but instead the entire sheet .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply .Apply End With End Sub
b) Then I would Like to filter out "Row O" by value (or even better Cell color) if possible.
Sub NAFilter() ' Rows("3:3").Select Range("O3").Activate 'Selection.AutoFilter Selection.AutoFilter Field:=18, Criteria1:="#N/A" Range(Selection, Selection.End(xlDown)).Select End Sub
and make sure to work still if there are no #N/A or colored cells
(mine works, but I know it can be better and I am not sure if both macros/ideas can be combined into one)
Feel free to contribute to either or of the macros (if you are not sure how to do both of the ideas in one VBA alone.) Thanks!