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)

Range("§BEGINNING OF DATA CELL LOCATION§").AutoFilter
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 _
:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("§WORKSHEET NAME§").AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


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

Report •


#1
Report •
Related Solutions


Ask Question