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!

There's a couple things I see that might be worth some time &
attention...

A dynamic range doesn't allow for blank rows between its beginning/end
cells in the column that --defines-- the range as 'dynamic'. In your
sample data I assume colA defines the dynamic range and so this should
be no problem.
===

You say you can't change the formulas but I strongly suggest you do so
your project has what's needed to result the expected behavior built
into its design. For instance, you can modify both formulas to return
values that compliment your desired sorting preferences. For example,
change the formula in colB to return "Z" instead of a space character,
so those cells end up at the bottom. You can use ConditionalFormatting
to shade the font so the cell 'appears' empty.

This won't alter the computational part of your formula. It merely
alters the return value if the formula fails to compute. (Assumes
formula is wrapped in an IF() function)
===

<FWIW
I'm not sure why people insist on formulas to return a space character
when an empty string ("") is a better choice. Note that the space
character doesn't work in calculations; an empty string does work!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks for the response. I agree; I can probably think of a better way to use formulas in this file. I will play with them and see what I can figure out. If anyone is willing to take a look, here is a link to my file:

http://www.datafilehost.com/download-5f5b42ab.html