CODEWITHSUNDEEP
vbaexcel
Linga
Linga

Reputation: 955

adding set of specific range values in list box in respective columns

I'm displaying set of range values in two list boxes using VBA form. In listbox1 headers, then per input displaying relevant data in listbox2 by using the below code:

'header
Set rng = sht.Range("A1:I1")

    With ListBox1
        .ColumnCount = 9
        .ColumnWidths = "50;40;30;120;160;120;50;30;30"
        .RowSource = rng.Address
    End With
'dynamic data 
Set rng1 = sht.Range(Cells(st, ukey), Cells(st + en - 1, utim))

With ListBox2
    .ColumnCount = 9
    .ColumnWidths = "50;40;30;120;160;120;50;30;30"
    .RowSource = rng1.Address
End With

How can I add the header and data in one listbox after the heading?

Also how can I add column 10 to 15 in the listbox from another set of ranges?

Upvotes: 1

Views: 1723

Answers (2)

Linga
Linga

Reputation: 955

I merged the ranges by using UNION as mentioned above by Vityata and moved them to a temp workbook and displayed by using the below code :)

Set rngt1 = sht.Range("A1:I1")
Set rngt2 = sht.Range("X1:AE1")
Set rngt3 = sht.Range("AI1")

Set rngd1 = sht.Range(Cells(st, ukey), Cells(st + en - 1, utim))
Set rngd2 = sht.Range(Cells(st, ccat), Cells(st + en - 1, etag))
Set rngd3 = sht.Range(Cells(st, comt), Cells(st + en - 1, comt))

Set rngt = Union(rngt2, rngt1)
Set rngt = Union(rngt3, rngt)
Set rngd = Union(rngd2, rngd1)
Set rngd = Union(rngd3, rngd)

Workbooks.Add
rngt.Copy ActiveSheet.Range("A1")
rngd.Copy ActiveSheet.Range("A2")

Set rng = ActiveSheet.Range(Cells(2, 1), Cells(en + 1, 18))
  

    With ListBox2
        .ColumnCount = 18
        .RowSource = rng.Address
        .ColumnHeads = True
    End With

Thank you Vityata for the help :)

Upvotes: 0

Vityata
Vityata

Reputation: 43595

How to add the header and data in one listbox after the heading?

Unite the two ranges, representing the header and the values like this

Set rng = Union(rng, rng1)
.RowSource = rng.Address

This is some small sample that works, combining both ranges into one listbox:

Private Sub UserForm_Activate()

    Dim rng         As Range
    Dim rngTitle    As Range

    Set rng = Range("D2:L3")
    Set rngTitle = Range("D1:L1")

    Set rng = Union(rng, rngTitle)

    With ListBox1
        .ColumnCount = 9
        .ColumnWidths = "50;40;30;120;160;120;50;30;30"
        .RowSource = rng.Address
    End With

End Sub

This is what I get:

enter image description here

Upvotes: 2

Related Questions