Application.AddCustomList, Method Error

[niko79542]

Hi,

Why am I getting an error message here?

deleteme2.xlsx

Sub macro1()

Application.AddCustomList Sheets(1).Range("J1:J7")
'Range("A1").CurrentRegion.Sort key1:=Range("A1"), OrderCustom:=Application.CustomListCount + 1
'Application.DeleteCustomList Application.CustomListCount

End Sub

[shg]

You can't delete the first four lists.

[niko79542]

Sorry, its not clear. Notice the ' before the last couple lines of code.

When trying to just add J1:J7 as a custom list I get the error, "Method 'AddCustomList' of object '_Application' failed"

[niko79542]

No, I have numbers in those cells "J1:J7"

How can I sort column A...for example...using "J1:J7" as a custom filter?

[shg]

You have text in those cells? That's what custom lists are made of.

[JosephP]

convert the numbers (eg prefix them with an apostrophe) to text then create the list

[niko79542]

Thx bro,

I didn't think of that...I kept trying to add characters at the end of the number which wasn't quite working out..Anywayz heres the correct code.

Niko

Sub macro1()
Dim i As Integer

For i = 1 To 7

Cells(i, 10) = "'" & Cells(i, 10)
Next i

Application.AddCustomList Sheets(1).Range("J1:J7")
Range("A1").CurrentRegion.Sort key1:=Range("A1"), OrderCustom:=Application.CustomListCount + 1
Application.DeleteCustomList Application.CustomListCount

End Sub