Macro to sort values in list without 0's (zeros)

[stacrafty]

Hi

I have a some lists where I have made a macro so the user can choose to
add a record to those lists and the macro goes and adds the record to
the bottom of the list. The list in then picked up by drop down menus
where the user can select a record.

The problem is when the user adds a new record, it gets put at the
bottom of the list and will thus show up at the bottom of the drop down
box. I don't know much VB, but I usually get around this by recording a
macro and then copying the code so it activates when my command button
is pushed.

So my list has zeros in it, and I want it sorted A-Z, if i do this is
will put the zeros at the top, then alphabetical. I want alphabetical
with the zeros at the bottom. This would invlove 2 sorts, one on the
whole list, Z-A which would put the zeros at the bottom, and then one
A-Z on the part of the list with no zeros. But the part without the
zeros is never going to be the same as more records are added...

Any know some vba code I could copy in to fix this?

Thanks

 

[Tom Ogilvy]

Your code would need to search through you list and ascertain where the
extent of the data is that would need to be included in each operation.

if the values are all hardcoded as it appears they are, you can possibly
utilize the capabilities of special cells

set rngAZ = columns(2).Specialcells(xlconstants,xlTextValues)
and/or
set rngZero = Columns(2).Specialcells(xlConstants,xlNumbers)