sorting so that blank records go to the top

[JulieD]

wouldn't have thought this was hard but i'm obviously missing something -

Categories Items
Category A Item 1
Category B Item 1
Category A Item 2
<blank> Item 2
<blank> Item 3

I want to see my list

Categories Items
<blank> Item 2
<blank> Item 3
Category A Item 1
Category A Item 2
Category B Item 1

and i just can't get it to happen - any ideas.

Cheers
JulieD

 

[JulieD]

Hi Camilo

very creative
problem is that i need to do the sort from within vba and i think this
approach will get too messy - but thanks anyway.

Cheers
JulieD

As you know, Blanks are always placed last. The following is a creative
solution to the problem.

Select the field/column that you want to sort (ie. A1:A20). On the Edit
menu, click Go To. Click the Special button. In the Go To Special dialog
box, click on Blanks. Once the blank cells are selected, hit the apostrophe
key (or some other character of your choice; see default sort order below)
and then hit Ctl + Enter (to fill all selected cells with the contents of the
active cell). If the field you are sorting contains only text, then an
ascending sort will accomplish what you want.


0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { |
} ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Hope this Helps!
Camilo

Objective: To help one person each day!

Feedback is greatly appreciated! Please let me know if I've answered your
question or if my post was helpful to you?

wouldn't have thought this was hard but i'm obviously missing something -

Categories Items
Category A Item 1
Category B Item 1
Category A Item 2
<blank> Item 2
<blank> Item 3

I want to see my list

Categories Items
<blank> Item 2
<blank> Item 3
Category A Item 1
Category A Item 2
Category B Item 1

and i just can't get it to happen - any ideas.

Cheers
JulieD

 

[Dave Peterson]

In code (record a macro when you do it manually):


Edit|replace
what: (leave blank)
replace: whateverstringwillsorttothetop
(replace all)

do the sort
edit|replace
what: whateverstringwillsorttothetop
with: (leave blank)
(replace all)

Another option is to just move the blanks to the top after you sort.

Find the last used row of a column that always has data.
find the last used row of the key column (and add 1)
take those rows and move them to the top.

Hi Camilo

very creative
problem is that i need to do the sort from within vba and i think this
approach will get too messy - but thanks anyway.

Cheers
JulieD

As you know, Blanks are always placed last. The following is a creative
solution to the problem.

Select the field/column that you want to sort (ie. A1:A20). On the Edit
menu, click Go To. Click the Special button. In the Go To Special dialog
box, click on Blanks. Once the blank cells are selected, hit the apostrophe
key (or some other character of your choice; see default sort order below)
and then hit Ctl + Enter (to fill all selected cells with the contents of the
active cell). If the field you are sorting contains only text, then an
ascending sort will accomplish what you want.


0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { |
} ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Hope this Helps!
Camilo

Objective: To help one person each day!

Feedback is greatly appreciated! Please let me know if I've answered your
question or if my post was helpful to you?

wouldn't have thought this was hard but i'm obviously missing something -

Categories Items
Category A Item 1
Category B Item 1
Category A Item 2
<blank> Item 2
<blank> Item 3

I want to see my list

Categories Items
<blank> Item 2
<blank> Item 3
Category A Item 1
Category A Item 2
Category B Item 1

and i just can't get it to happen - any ideas.

Cheers
JulieD