Macro that sorts data with blanks at the bottom

[abbruno]

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

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

Column A contains 7777xxxxxxx-Branch Name and Column B includes a grou
identifier, i.e. MWBF2. I cannot seem to get the columns to align i
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 botto
because column B is blank.

Can anyone help me?! Thank you very much

 

[GS]

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