Automated alphabetical drop-down list in VBA


New Member
Nov 23, 2017
Hello all,

This is one of my first posts, so please let me know if I'm not doing it correctly!
I read the forum manual but still, I am human and I make mistakes..
My previous post was approved so I hope this one will be as well.

So here it goes..

I have a userform which can copy specified columns of a specified workbook, to specified columns in the "active" sheet.
You can also choose to copy the whole sheet from one to the other.
It is used in a sort of inventory/stock managing system.
Now, in some columns there are duplicate values, which I don't need and would like to delete.
But I don't need to delete all duplicate values over the whole Sheet. So, some columns should still have duplicates.
My question is, is there a way to make a drop-down list on a userform, that has all the possible column letters in it, by itself?
So without making an extra sheet with a column that has all the letters and then let that populate the drop-down list.
So that you can specify in which column the duplicates should be deleted.

Is there VBA code that I can put in my UserForm that will auto-populate the drop-down list with all the column letters?
There, that is my question in short.

I know I can use an Inputbox and then let the user fill in the letter of the column. I was just wondering if such a thing is possible.
Because, if I let the user fill it in, I need to let it be checked for errors.

-Using Office 2010

Hope this is clear? Or do you need more information?
I don't think it's necessary to add my code/snippets?
If so, let me know and I'll post it!
Sorry if my English is not 100%, not my mother tongue!

Kind regards, and thanks in advance for any help/comment!


Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.


MrExcel MVP, Moderator
Jun 12, 2014
Office Version
  1. 365
  1. Windows
If your data never goes beyond col Z you can use this
Private Sub UserForm_initialize()
   Dim lc As Long, i As Long
   Dim lst As String
   lc = Cells(4, Columns.Count).End(xlToLeft).Column
   lst = "A"
   For i = 2 To lc
      lst = lst & "," & Chr(i + 64)
   Next i
   Me.ComboBox1.List = Split(lst, ",")
End Sub


MrExcel MVP
Jan 9, 2008
Try this:-
This code should cover you Usedrange columns count
Private Sub UserForm_Initialize()
Dim a As Long, b As Long, c As Long
Dim lc As String, lb As String, Num As Long
For c = 0 To 24
    For b = 0 To 26
        For a = 1 To 26
            Num = Num + 1
            If Num > ActiveSheet.UsedRange.Columns.Count Then Exit Sub
            If c = 0 Then lc = "" Else lc = Chr(64 + c)
            If b = 0 Then lb = "" Else lb = Chr(64 + b)
                ComboBox1.AddItem Trim(lc & lb & Chr(64 + a))
        Next a
    Next b
Next c
Last edited:


New Member
Nov 23, 2017
@Fluff and @MickG
I will try this first thing tomorrow when I go back to work!
Thanks in advance, from what I can see (and run this VBA in my head), this indeed should do what I want.
I'm not sure, but I don't think the data will exceed column Z, but I will try both solutions and see which one fits best.
Thanks thanks thanks!
I will post my findings and results!



New Member
Nov 23, 2017
Both of the solutions provided by these MrExcel MVP's are working!
And doing exactly what they said, and what I need.
I'll be using the answer given by MickG, but only for the sake of being prepared if the columns go over "Z" :)

Thank you both for the fast answer and the useable solutions!

King regards,

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics