I am trying to add a macro that finds a dynamic range (number of rows changing with different data sets) sorts column B, then column A, moving blanks to the bottom. My dilemma is as follows:

I have a template with formulas that cannot be changed (I cannot paste special values, or clear the cells). Column A includes HLOOKUP formulas for cells A14:A120. Column B includes VLOOKUP formulas for cells B14:B120. The different data sets I pull in have different numbers of lines, which is why I need to maintain the formulas. However, I cannot seem to find a way to accurately sort column B first from smallest to largest and then column A from smallest to largest. In all data sets there will be at least a few blank lines that I need to move to the bottom. However, due to the formulas within the cells (column A returns a value of 0 and column B returns a value of “ “), excel does not recognize them as being blank. Here is a sample data set.

Column A contains 7777xxxxxxx-Branch Name and Column B includes a group identifier, i.e. MWBF2. I cannot seem to get the columns to align in the post.


Branch S&C Group
77770000583-DOWNERS GROVE BRANCH MWBF2
77770000585-CHICAGO RIVER BRANCH MWBF1
77770000587-WEST TOWN BRANCH MWBF1
77770000941-DES PLAINES 750 LEE BRANCH MWBF1
77770000942-DES PLAINES OAKTON BRANCH MWBF1
77770000944-LINCOLN PARK BRANCH MWBF2
77770002244-ROSEMONT BRANCH (ILLI3103) MWBF2
77770002247-VERNON HILLS BRANCH (ILLI3168) MWBF2
77770002250-ELK GROVE BRANCH (ILLI3104) MWBF2
77770002253-NILES BRANCH (ILLI3181) MWBF2
77770002258-DEERFIELD BRANCH (ILLI3165) MWBF1
77770002267-ST. CHARLES, IL
77770002268-FOX LAKE BRANCH (ILLI3169) MWBF1
77770002271-CRYSTAL LAKE BRANCH (ILLI3180) MWBF2
77770002272-ALGONQUIN, IL
77770002274-WHEELING BRANCH (ILLI3183) MWBF2

In this case, I would like St. Charles & Algonquin to be at the bottom because column B is blank.

Can anyone help me?! Thank you very much!