VBA ListBox List - Want only selected columns to be listed

[vmjamshad]

Hi All,

ListBox1.ColumnCount = 4
ListBox1.ColumnWidths = "25;120;60;80"
Me.ListBox1.list = Sheets("Sheet1").Range("A2:AF100").Value

My data range is ("A2:BB100")
Here in this list box, I want only 2 columns (Column A and Column AF)
Can anybody help me to solve this,

1. I want to use variables for list range and data range. The ranges will vary.

Thanks - Jamshad

 

[ranman256]

set BA1=A1, etc
set BB1=AF1, etc

make the listbox range= BA,BB

 

[vmjamshad]

set BA1=A1, etc
set BB1=AF1, etc

make the listbox range= BA,BB

Thanks rabman
Could you please explain with the codes

 

[Norie]

If you only want 2 columns in the listbox why are you setting ColumnCount to 4?

 

[vmjamshad]

If you only want 2 columns in the listbox why are you setting ColumnCount to 4?

Hi Norie, that is just as an example. I have around 300 columns and need to select only few columns -may be around 10. that also will not be in sequence. And the row length will keep on changing. So I will have to use a dynamic data range.

 

[vmjamshad]

Solved with the below code:

Private Sub UserForm_Initialize()
With ListBox1
    .ColumnCount = 4
    .ColumnWidths = "130;30;30;130"
End With


LstRow = Cells(Rows.Count, 1).End(xlUp).Row
For a = 0 To LstRow - 2
    b = a + 2
    ListBox1.AddItem
    ListBox1.list(a, 0) = Cells(b, 4)
    ListBox1.list(a, 1) = Cells(b, 1)
    ListBox1.list(a, 2) = Cells(b, 3)
    ListBox1.list(a, 3) = Cells(b, 2)


Next a


End Sub

Thanks - Jamshad