Loop Through Used Range of specific Column in VBA Excel

Question

I want to get values of all cells in a specific column and add them to a list Here is what I have tried

Dim rng As Range
'here ColumnName will be like "A", "B" etc...
'and SheetName will be like "Sheet001", "Sheett002" etc...
rng = ThisWorkbook.Sheets(SheetName).Columns(ColumnName).UsedRange
For Each cell In rng.Cells
If Not cell.Value = "" Then
ListBox1.AddItem (cell.Value)
End If
Next

But I am not being able to determine how to get used range of column by its name. The data I want to read will be like this in a separate sheet

Answer 1

UsedRange will refer to a used range of a sheet. With the UsedRange you can then select the required Columns.

I have modified your code a bit. See below:

Sub SomeSub()
    Dim MySht As Worksheet
    Dim MyRng As Range

    Set MySht = ThisWorkbook.Sheets("Sheet1")
    Set MyRng = MySht.UsedRange.Columns("A")

    For Each cell In MyRng.Cells

        If Not cell = "" Then
          'If your List Box is in "Sheet1"
          Sheets("Sheet1").ListBox1.AddItem (cell)
          'If your List Box is in "UserForm1"
          UserForm1.ListBox1.AddItem (cell)
        End If
    Next

    'To clear ListBox1 data
'    Sheets("Sheet1").ListBox1.Clear
'    UserForm1.ListBox1.Clear

End Sub