Thread: Sorting numbers with #N/A

I am trying to set up a macro to sort descending but to have any cells that are "N/A" to be sorted at the bottom.

An example:

Data:
5
7
4
N/A

When using descending "Sort" in excel, the data is arranged:
N/A
7
5
4

I would like it to sort as follows:
7
5
4
N/A

Thanks!

Hi dluhut

I would always look at the errors to see why you are getting them and look to Excel to replace them with either zeros or blanks. Then you can sort away without any problems.

Try this post here for some guidance

Hope that helps
Barbara

http://howtoexcelatexcel.com

Thanks to those who replied.

To be more specific, I've attached a file so that you guys could understand.

Noticed that on the file, I filtered on Column E to purposefully hide some rows, reason being is that in the original file that I'll be doing, I have a code that filters showing only datas that I want to see based on the dropdown list, and then follow by another macro/code that will do the sorting.

The sorting will then be like what I've described above, which is putting numbers first in descending orders before the #N/A.

Below are the codes that I need to tweak in order to let the sorting works after I've done filtering.

Sub SELECT_PLATINUM_CLUB()
  Selection.AutoFilter _
        Field:=1, _
        Criteria1:=ThisWorkbook.Worksheets("drop down list").Range("d2").Value, _
        Operator:=xlAnd
        
  Dim rngToSort   As Range
    Dim naRow       As Long
    Dim LastRow     As Integer

    LastRow = Range("B" & Rows.Count).End(xlUp).Row

    Set rngToSort = Range("B16:P" & LastRow)
    
    With rngToSort
        .Sort .Cells(2, 12), 1, Header:=1 'first sort Col M by ascending
        On Error Resume Next
        naRow = .SpecialCells(2, 20).Row
        If naRow = 0 Then naRow = .SpecialCells(-4123, 20).Row
        On Error GoTo 0
        If naRow Then
            With .Cells(1, 1).Resize(naRow - .Row, .Columns.Count)
                .Sort .Cells(2, 12), 2, Header:=1 'then sort Col M by descending
            End With
        Else
            .Sort .Cells(2, 12), 2, Header:=1 'if no error cells, sort again Col M by descending
        End If
    End With

End Sub

Any Mod,

You can mark this post solved as per the link I provided in my last post the user has a working script from the other forum.

Thank You, Mike