I tried looking through Google but to no avail. I'm
trying to automatically sort a range of data containing
two columns using VBA not the sort under DataSort. The
cell I'm want to sort on is text (company names) and is a
formula based on information on another worksheet. My
dilema is that when I sort this way
..Range("a34:b202").Sort Key1:=.Range("a34"),
Order1:=xlAscending
the results come out alphabetically but if there are
blank cells between the last company and A202 the blanks
are at the beginning and I need them at the bottom. Is
there a way to sort with putting the blanks at the end?
Could I maybe add INDEX or something to the formula to
have it do it? the fomula I use is:
{=IF(ISNA(INDEX('Priced BOM'!$W$3:$W$1010,MATCH(0,COUNTIF
($A$33:A33,'Priced BOM'!$W$3:$W$1010),0))),"",INDEX
('Priced BOM'!$W$3:$W$1010,MATCH(0,COUNTIF
($A$33:A33,'Priced BOM'!$W$3:$W$1010),0)))}
Thanks for all your help
Joe

I might consider putting "zzzzzzzzzzzzzzzz"s (or other useful but unique
values) in the blank cells programatically, sort, then remove the
"zzzzzzzzzzzzzzzzzz"s using a replace the "zzz...." with a null string.

Or use a helper column filled with formulas and/or values that better
represents the sort I want, sort on the helper column, then drop it.

"Joe Gieder" wrote in message
...
I tried looking through Google but to no avail. I'm
trying to automatically sort a range of data containing
two columns using VBA not the sort under DataSort. The
cell I'm want to sort on is text (company names) and is a
formula based on information on another worksheet. My
dilema is that when I sort this way
.Range("a34:b202").Sort Key1:=.Range("a34"),
Order1:=xlAscending
the results come out alphabetically but if there are
blank cells between the last company and A202 the blanks
are at the beginning and I need them at the bottom. Is
there a way to sort with putting the blanks at the end?
Could I maybe add INDEX or something to the formula to
have it do it? the fomula I use is:
{=IF(ISNA(INDEX('Priced BOM'!$W$3:$W$1010,MATCH(0,COUNTIF
($A$33:A33,'Priced BOM'!$W$3:$W$1010),0))),"",INDEX
('Priced BOM'!$W$3:$W$1010,MATCH(0,COUNTIF
($A$33:A33,'Priced BOM'!$W$3:$W$1010),0)))}
Thanks for all your help
Joe