1

I am trying to run a macro that

a) sorts numerically by column E and G
b) and then sorts by column O

a) 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!

| improve this question | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.