Excel VBA macro to create custom lists from a selection

Question

Hi I was wondering if anyone could help me create a macro that will add a selection to excels custom list. Typically to do this I select a a series of rows in one column, then I got to file, options, advanced and scroll to the very bottom and click edit custom lists. When going through this with the macro creator turned on all I get is a very simple script that is related to the specific cells I have selected. I would like to tweak the code so that it is using whatever I have currently selected to add to the custom list. That way I am not always tied down to the J4-J9 Range. Below is the code I receive.

Sub Customlistadd()
'
' Customlistadd Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
    Application.AddCustomList ListArray:=Range("J4:J8")
End Sub

Is it as simple as adding current selection in place of j4:j8? If so how do I do I write that exactly? Do I first need to declare a variable type to hold the current section? Almost all examples I've looked at that seem to be attempting to do something along the lines of what I am trying to do tend to use variables to hold the current selections range. Thank you.

Solution 1

try this with add , get and delete custom list. Tip: Blank cells treat as countable order,kindly don't use blank cells

Sub CoustomList()

    Dim Arr() As Variant
    Dim N As Long    
    Application.AddCustomList ListArray:=Sheet1.Range("A2:A4")
    Arr = Sheet1.Range("A2:A4")
    N = Application.GetCustomListNum(Arr)
    Application.DeleteCustomList N

End Sub

Solution 2

Change your code to use the Selection object, which will always contain the currently selected cells (if more than 1 is selected). i.e.

Application.AddCustomList Selection

And make sure your custom list only contains letters, not numbers. e.g. A,B,C,D,E,F,G,... or A1,A2,A3,A4,A5,A6,... are fine, but 1,2,3,4,5 isn't.

Solution 3

Try this

Public Sub CreateCustomList()
  Dim v As Variant
  Dim rng As Excel.Range

  Set rng = ActiveSheet.Range("A1:A3")

  'Transpose from 2 dim array to 1 dim array
  v = Application.Transpose(Selection)

  'Add the custom list
  Application.AddCustomList v

End Sub

Comments

[small3687]

I have tried the following combinations, none of which have run successfully. Could you provide a little more clarification Application.AddCustomList ListArray:=Range("selection") Application.AddCustomList ListArray:=Range(selection) Application.AddCustomList ListArray:=Range selection" Application.AddCustomList ListArray:= selection Application.AddCustomList selection None the combinations I have have tried run correctly. I know I must be screwing up the syntax but I just can't figure out where.

[teylyn]

Just use the command as Rhys Gibson has posted. No ListArray or Range. Just use Application.AddCustomList Selection

[small3687]

Unfortunately that is how I have already tried to write the code but it fails to run. Sub Customlistadd() ' ' Customlistadd Macro ' ' Keyboard Shortcut: Ctrl+Shift+I ' Application.AddCustomList Selection End Sub That is the entirety of my code and I get stopped on the line with the Selection object. Any other ideas? I end up receiving a run time error 1004

[Rhys Gibson]

Make sure your custom list only contains letters, not numbers. e.g. A,B,C,D,E,F,G,... is fine, but 1,2,3,4,5 isn't.

[small3687]

They are 20 digit strings of numbers that are formatted as text. Do you think that is the problem?

[Rhys Gibson]

Possibly. can you post an example?