Difficulty with sorting data with blank formulas? Try this.

March 7, 2013 at 11:08:00
Specs: Windows 7
I've seen a few answers on here for sorting data with blank formulas. Some said to copy the blanks and move them through a macro to the bottom, or to move them to the top, then hide. The easiest way I have found is to use the Autofilter feature in Excel to hide the blanks and sort the way you want. This way anything with the values in it will show up, but all blanks would disappear. Make sure to set your first data column to a field that will always have data in it, or this won't work the way you want. Here's the VB code I used in the Macro.

(Variables noted with § symbols)

ActiveSheet.Range("§RANGE OF DATA§").AutoFilter Field:=1, Criteria1:="<>"
ActiveWorkbook.Worksheets("§WORKSHEET NAME§").AutoFilter.sort.SortFields.Clear
ActiveWorkbook.Worksheets("§WORKSHEET NAME§").AutoFilter.sort.SortFields.Add Key:= _
Range("§COLUMN TO BE SORTED BY§"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
With ActiveWorkbook.Worksheets("§WORKSHEET NAME§").AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With

See More: Difficulty with sorting data with blank formulas? Try this.

Report •
Related Solutions

Ask Question