PDA

View Full Version : VBA ListBox List - Want only selected columns to be displayed



vmjamshad
10-23-2017, 10:48 PM
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
10-24-2017, 01:44 AM
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
10-24-2017, 02:44 AM
Woow.. Thanks. Its working

MINCUS1308
10-24-2017, 03:55 AM
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
10-24-2017, 04:18 AM
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
10-24-2017, 06:01 AM
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
10-24-2017, 06:42 AM
Seems to work. Go for it.

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

MINCUS1308
10-24-2017, 06:46 AM
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
10-24-2017, 08:07 AM
@MINCUS1308 -- you need to change your sig - it's obviously wrong, since you do know what you're doing -- don't be humble :thumb

MINCUS1308
10-24-2017, 09:10 AM
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 :p

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

vmjamshad
10-24-2017, 09:26 AM
Thanks MINCUS1308 .. I tried that, but got some error. Thanks Jamshad

MINCUS1308
10-24-2017, 10:36 AM
that's unfortunate. can you describe the error or post a screen shot ? maybe post your file?

vmjamshad
10-24-2017, 08:05 PM
I have attached the file.

mancubus
10-25-2017, 12:05 AM
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
10-25-2017, 01:04 AM
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
10-25-2017, 04:17 AM
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
10-25-2017, 05:19 AM
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
10-30-2017, 04:03 AM
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
10-30-2017, 04:43 AM
Please use code tags !!

vmjamshad
10-30-2017, 04:55 AM
You meant for the column and cell numbers, Right?

Please use code tags !!

Paul_Hossler
10-30-2017, 06:30 AM
Actually if you use the[#] icon above the message area, it will paste in
... macro code goes here ... so you can paste/write your macro between the tags

It formats and sets the code off for better readability





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

vmjamshad
10-30-2017, 08:02 AM
Thanks Paul, Just realized that. I am new to this.