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.
<tbody>
</tbody>
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!
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.
| 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 |
| 77770002278-HIGHWOOD BRANCH (ILLI3160) | MWBF2 |
| 77770002281-MUNDELEIN BRANCH (ILLI3163) | MWBF2 |
| 77770002284-PALATINE BRANCH (ILLI3164) | MWBF2 |
| 77770002291-25 E WASHINGTON BRANCH-IL | MWBF1 |
| 77770002294-PARK FOREST BRANCH (ILLI3101) | MWBF1 |
| 77770002297-BURBANK BRANCH (ILLI3102) | MWBF2 |
| 77770002301-BELMONT BRANCH (ILLI3116) | MWBF1 |
| 77770002304-EDGEWATER BRANCH (ILLI3119) | MWBF1 |
| 77770002307-IRVING PARK IL | MWBF2 |
| 77770002313-OAK PARK BRANCH (ILLI3111) | MWBF1 |
| 77770002316-AUSTIN BRANCH (ILLI3113) | MWBF2 |
| 77770002324-ELMHURST BRANCH (ILLI3144) | MWBF1 |
| 77770002333-NORTHLAKE BRANCH (ILLI3148) | MWBF2 |
| 77770002337-GENEVA BRANCH | MWBF1 |
| 77770002340-EAST GENEVA BRANCH (ILLI3138) | MWBF2 |
| 77770002350-ROOSEVELT RD BRANCH (ILLI3153) | MWBF2 |
| 77770002353-GLEN ELLYN MAIN OFFICE | MWBF1 |
| 77770002360-NAPERVILLE BRANCH (ILLI3131) | MWBF1 |
| 77770002366-MARKET MEADOWS BRNCH(ILLI3133) | MWBF1 |
| 77770002369-BOLINGBROOK BRANCH (ILLI3134) | MWBF1 |
| 77770002372-63RD STREET BRANCH | MWBF2 |
| 77770002378-WHITE EAGLE BRANCH (ILLI3135) | MWBF1 |
| 77770003974-ROOKERY BLDG-IL | MWBF2 |
| 77770007140-LEMONT IL | MWBFP |
| 77770007141-AUSTIN-DIVISION ST IL | MWBFP |
| 77770007142-COSMOPOLITAN IL | MWBFP |
| 77770007143-EAST SIDE IL | MWBFP |
| 77770007144-ENGLEWOOD IL | MWBFP |
| 77770007146-MILWAUKEE AVE IL | MWBFP |
| 77770007147-MOUNT GREENWOOD IL | MWBFP |
| 77770007148-NORWOOD PARK IL | MWBFP |
| 77770007149-PULLMAN IL | MWBFP |
| 77770007150-SOUTH CHICAGO IL | MWBFP |
| 77770007151-WEST GARFIELD IL | MWBFP |
| 77770007152-CICERO IL | MWBFP |
| 77770007156-HINSDALE IL | MWBFP |
| 77770007157-LISLE IL | MWBFP |
| 77770007159-MAYWOOD IL | MWBFP |
| 77770007161-NORTH OAK PARK IL | MWBFP |
| 77770007162-OAK PARK-MADISON ST IL | MWBFP |
| 77770007163-CHICAGO HEIGHTS IL | MWBFP |
| 77770007164-DOLTON IL | MWBFP |
| 77770007165-FLOSSMOOR IL | MWBFP |
| 77770007166-HOMEWOOD IL | MWBFP |
| 77770007167-LANSING IL | MWBFP |
| 77770007168-SAUK VILLAGE IL | MWBFP |
| 77770007170-WEST DUNDEE IL | MWBFP |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 | |
| 0 |
<tbody>
</tbody>
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!
