How Do I Ignore Blanks When Automatically Sorting In Excel with VBA

[Dokat]

I have a vba code that automatically sorts my table in descending sales order based on column P. However when it sorts it brings all the blank fields to the top....How can i modify the code where it ignores blank rows and sorts in descending order for fields with values.

All the cells including blank ones has formulas in them so i dont want to delete them.

Private Sub Worksheet_Change(ByVal Target As Range)

    Range("N22:S22").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("POS Tracker").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("POS Tracker").Sort.SortFields.Add Key:=Range( _
        "P23:P69"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("POS Tracker").Sort
        .SetRange Range("N22:S69")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks

 

[PCosta87]

I have a vba code that automatically sorts my table in descending sales order based on column P. However when it sorts it brings all the blank fields to the top....How can i modify the code where it ignores blank rows and sorts in descending order for fields with values.

All the cells including blank ones has formulas in them so i dont want to delete them.

Private Sub Worksheet_Change(ByVal Target As Range)

    Range("N22:S22").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("POS Tracker").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("POS Tracker").Sort.SortFields.Add Key:=Range( _
        "P23:P69"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("POS Tracker").Sort
        .SetRange Range("N22:S69")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks

Hi,

Since it is sorting values in descending order, I would just modify the formulas to return 0 (without quotation marks) instead of "".

Hope this helps

 

[vletm]

... If 'blanks' are really 'blanks' then this work but if there is something like 0 then as above.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With Sheets("POS Tracker")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("P23:P69"), SortOn:=xlSortOnValues, _
           Order:=xlDescending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("N22:S69")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Dokat]

There are no 0's however they are not true blanks there is iferror formula in the cell...if the value is NA it returns "" blank.

 

[NARAYANK991]

Hi ,

I think one way is that if all the blank rows are bunched together at the end of the data rows , then identify this end row , and redefine the sort range accordingly.

Narayan

 

[PCosta87]

There are no 0's however they are not true blanks there is iferror formula in the cell...if the value is NA it returns "" blank.

Hi,

Have you tried my suggestion?... replace the "" in the IFERROR formula with 0 (without quotation marks) and those cells will be placed at the bottom when sorting because they will have value 0 and you are sorting in descending order.

Does it help in any way?