VBA Sort Data and Treat N/A as Zero

unknownymous

Board Regular
Joined
Sep 19, 2017
Messages
186
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
what is the formula in A that can return N/A

try this
Code:
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
 
Last edited:

unknownymous

Board Regular
Joined
Sep 19, 2017
Messages
186
Office Version
  1. 2016
Platform
  1. Windows
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,126
Messages
5,628,858
Members
416,345
Latest member
sayad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top