VBA Sort Data and Treat N/A as Zero

[unknownymous]

Hi Team,

Can you possibly help me simply the recorded codes below:

The range must be until the last non-blank sheet and if N/A was found on the cell it will be treated as zero when sorting in descending form.

==============================

Sub Macro1()
'
' Macro1 Macro
'


'
Rows("5:5").Select

Range("A5:A1956").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("5:1048576").Select
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"E5:E1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"F5:F1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"I5:I1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"J5:J1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"K5:K1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"L5:L1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"M5:M1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"N5:N1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"O5:O1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"P5:P1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"Q5:Q1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"R5:R1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"S5:S1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Data Sheet").Sort.SortFields.Add Key:=Range( _
"T5:T1909"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Data Sheet").Sort
.SetRange Range("A5:AH1909")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A5").Select
End Sub

==============================

Thanks for the help.

 

[mole999]

what is the formula in A that can return N/A

try this

Sub Macro1()
    Dim LR As Long
    LR = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    '  Rows("5:5").Select

    '  Range("A5:A1956").Select
    '  Range(Selection, Selection.End(xlDown)).Select
    '  Rows("5:1048576").Select
    With ActiveWorkbook.Worksheets("Data Sheet")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("E5:E" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("f5:f" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("i5:i" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("j5:j" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("k5:k" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("l5:l" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("m5:m" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("n5:n" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("o5:o" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("p5:p" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("q5:q" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("r5:r" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("s5:s" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("t5:t" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    End With

    With ActiveWorkbook.Worksheets("Data Sheet").Sort
        .SetRange Range("A5:AH" & LR)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A5").Select
End Sub

 

[unknownymous]

Thanks for the feedback mole999. I experienced an error but I just added this "Set sht = ActiveSheet" and all seems fine.

As for the N/A, those are already in the file and harcoded already so there's no formula. If there's any way the macro can treat it as zero when sorting, that would be great.


Thanks for the help.

 

[mole999]

you might consider a find replace (recorded) to change the NA to 0 and add that to the macro, its very quick, and won't do any damage