VBA ListBox List - Want only selected columns to be displayed

[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,

Thanks - Jamshad

[mancubus]

Col A's index is 1 and Col AF's index is 32
A2:BB100 is a 99-rows table

Private Sub UserForm_Initialize()

    sn = Range("A2:BB100").Value
    sp = Application.Index(sn, [Row(1:99)], Array(1, 32))
    'http://www.snb-vba.eu/VBA_Arrays_en.html#L_0
    'Title 6.7.1
    
    With ListBox1
        .ColumnCount = 4
        .ColumnWidths = "25;120;60;80"
        .List = sp
    End With

End Sub

[vmjamshad]

Woow.. Thanks. Its working

[MINCUS1308]

woow is right. mancubus and other Masters do some truly amazing things with code.
Welcome to the forum vmjamshad!
If you could, would you please mark this thread as solved for us?
I believe at the upper right corner of the thread there is a 'Thread Tools' drop down.
There should be a 'Mark as Solved' option.

[mancubus]

your are welcome.

credits go to snb... the link to the source is included in the code.


@jamshad
pls mark the thread as solved (see my signature) for future references...

[vmjamshad]

Dear - I have one more doubt. Can i apply variable to the row. [Row(1:99)] - [Row(1:a)] and a will be the last row of the column. The reason behind that is there will be addition and deletion to the file. So the row end number will keep changing.

Hope that my question is clear

Thanks

[MINCUS1308]

Seems to work. Go for it.

Sub testing()
     a = 99
     sp = Application.Index(sn, [Row(1:a)], Array(1, 32))
End Sub

[MINCUS1308]

if you want to set the variable to the last used column you can use

a =ActiveSheet.UsedRange.Columns.Count

but it is prone to error. maybe someone else has a better method

[Paul_Hossler]

@MINCUS1308 -- you need to change your sig - it's obviously wrong, since you do know what you're doing -- don't be humble

[MINCUS1308]

Yeah.. I maxed out my post number at 999,999. so I had to reset my user account. Now I have to climb my way back through the ranks

I fumble my way through a lot of code. But thank you Paul_Hossler

[vmjamshad]

Thanks MINCUS1308 .. I tried that, but got some error. Thanks Jamshad

[MINCUS1308]

that's unfortunate. can you describe the error or post a screen shot ? maybe post your file?

[vmjamshad]

I have attached the file.

[mancubus]

Private Sub UserForm_Initialize()
       
    With Worksheets("Sheet1")
        With .Range("B6:MX" & .Cells(Rows.Count, 1).End(xlUp).Row - 1)
            sn = .Value
            sp = Application.Index(sn, Evaluate("Row(" & .Offset(-5).Address & ")"), Array(1, 2))
        End With
    End With
        
    With ListBox1
        .ColumnCount = 7
        .ColumnWidths = "180;180;160;0;25;0;30"
        .list = sp
    End With

End Sub

1) think about Offset(-5). (why did i add that bit?)

2) homework: VBA Evaluate Method (Google it. there are many valuable online resources.)

[snb]

What's the use reading many columns if you only need the first 2 ? :array(1,2)

Private Sub UserForm_Initialize()
  With ListBox1
    .list = Sheet1.Cells(1).CurrentRegion.Offset(5, 1).Resize(, 1).SpecialCells(2).Resize(, 2).Value
    .ColumnCount = 2
    .ColumnWidths = "180;180"
  End With
End sub

If you want the first and the last column of a certain range to be part of - and to be shown in- a ListBox, you could use:

Private Sub Userform_initialize()
  With ListBox1
    .list = Sheet1.Cells(1).CurrentRegion.Offset(5, 1).Resize(, 1).SpecialCells(2).Resize(, 4).Value
    .list = Application.Index(.list, Evaluate("row(1:" & .ListCount & ")"), Array(1, UBound(.list, 2)))
    .ColumnCount = 2
    .ColumnWidths = "180;180"
  End With
End Sub

[vmjamshad]

Thanks. actually my file has more than 400 columns. From that i will select maximum of 10-12 columns which i will show in the list box.that is the reason why i am looking for array

[snb]

Adapt to your needs

For Example: if you want columns 1,11,21,31,41,51,61,71,81,91 to be displayed:

Private Sub Userform_initialize() 
  With ListBox1 
    .list = Sheet1.Cells(1).CurrentRegion.Offset(5, 1).Resize(, 1).SpecialCells(2).Resize(, 400).Value 
    .list = Application.Index(.list, Evaluate("row(1:" & .ListCount & ")"), Array(1,11,21,31,41,51,61,71,81,91)) 
    .ColumnCount = ubound(.list,2)+1 
    .ColumnWidths = mid(replace(space(.columncount)," ",";180"),2)
  End With 
End Sub

[vmjamshad]

Hi, I used 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
Please suggest if any changes to be made.
Thanks.

[snb]

Please use code tags !!

[vmjamshad]

You meant for the column and cell numbers, Right?

Please use code tags !!