Thanks:  0
Likes:  0

# Thread: i got this problem and would like to solve it, but i need yo

1. i got the following list, is there any way that i can have the largest number at the top and all the #N/A at the bottom, i tried so many times and it still won't work.
Please let me know if there is a way to overcome this problem:

15
117
151
152
215
237
249
266
270
279
345
361
481
573
660
880
1510
#N/A
#N/A
#N/A
#N/A
#N/A

2. Your initial sort put the #N/A at the bottom.

Select the range of just numbers and sort
descending.

3. 1. Sort the list in Ascending order
2. Select the list and choose Edit | Go To... Special Constants Numbers (checked only)
3. Sort the resultant selection in Descending order.

4. the method works, but still not quite to solve my problem, my actual problem is this:

A 1510
B 660
C 880
D 249
E 573
F 481
G 266
h #N/A
I 345
j #N/A
k 361
l 270
m 117
n 279
o 215
p 151
q 152
r 237
s 15
t #N/A
u #N/A
v #N/A

can u do a sort on this? the largest number at the top... pls help

5. i did, i have tried it , but it doesn't work to my actual problem...

A 1510
B 660
C 880
D 249
E 573
F 481
G 266
h #N/A
I 345
j #N/A
k 361
l 270
m 117

I can't get it sort, because it only select the number, but i need the letter next to it to be selected as well, so that i can have it sort.

the thing is i can't get it to do it, i need to save this as a macro, but excel can only read me selecting A2: B18 range, not the thing that i want...

pls help

6. Replace #N/A with blanks, sort descending, then re-enter #N/A in the blank cells.

I trust this won't change the 'actual problem'!

[ This Message was edited by: Mark W. on 2002-04-10 15:58 ]

7. how do u do it as a command, so that i can do it in my macro as well

can any ppl pls help me please??

[ This Message was edited by: LMF on 2002-04-10 16:01 ]

8. Ensure that you select all relevant columns (A and B?) and sort as mentioned above with primarly sort on Column B.

9. On 2002-04-10 15:59, LMF wrote:
how do u do it as a command, so that i can do it in my macro as well

can any ppl pls help me please??

[img]/board/images/smiles/icon_smile.gif[/img]

[ This Message was edited by: LMF on 2002-04-10 16:01 ]
Range("B1:B22").Select
Selection.SpecialCells(xlCellTypeConstants, 16).Select
Selection.ClearContents
Range("B1").Select
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B1:B22").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "#N/A"

10. i did what he said...
first select both columns A and B, sort Ascending to column B, then select constant as number only. it only select all the number on column B...

now, since i need to do sort with conlumn A as well, i select column A, but the problem is, i need to set this as a macro... and the excel can only read me doing it as manually.

means that it reads range("A2:B18"), which is not what i want in my macro. what happen if there are more #N/A, or less #N/A in the list???

so i really would like some1 can show me how this can be done???

pls let me know

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•