How to union multiple ranges and show it in a listbox of a VBA form

Question

I try to union multiple ranges, but the result is alway just two columns where it should be more.

    With ActiveSheet

      lastRow = .Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

      Set rng1 = .Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)
      Set rng2 = Union(.Range("A1:B" & lastRow), .Range("E1:F" & lastRow))
    
      With lb
        .List = rng1.Value
        .ColumnWidths = "100;200;100;100;100;100"
        .ColumnCount = 6
      End With
   End With

The above code is just for testing. When I bind rng1 to te listbox list property, it works as expected. Multiple columns show in the listbox.

When I bind rng2 to the listbox, only two columns are drawn (just the values of the first range).

What am I doing wrong here?

Black cat · Accepted Answer · 2025-09-13 13:31:44Z

2

In case of UNION of non-contiguous ranges the resulted range will have AREAS property. Generally methods take only the first area when executed. You have to iterate through on them, or find some other workaround on the issue.

In this usecase you can stay at the original assignment but "hide" the two not needed columns.

.ColumnWidths = "100;200;0;0;100;100"

edited Sep 13 at 13:31

answered Sep 13 at 13:27

Black cat

7,5698 gold badges66 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2025-09-13 15:23:29Z

Stack Range Areas Horizontally

If rng is a set range, then the expression rng.Value on the right side of an equation means that it is a 2D one-based array with the same values and the same number of rows and columns as the first area of that range unless the first area is a single cell when the expression is equal to the cell's value.
You could create a function that would return the values of a multi-area range in a single 2D one-based array. Then your code would look like this:

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng2 = Union(.Range("A1:B" & LastRow), .Range("E1:F" & LastRow))
    
    With lb
        .List = GetHStackedAreas(rng2)
        .ColumnWidths = "100;200;100;100"
        .ColumnCount = 4
    End With
End With

The Function

Function GetHStackedAreas(ByVal rg As Range) As Variant

    Dim AreasCount As Long: AreasCount = rg.Areas.Count
    Dim rcData() As Variant: ReDim rcData(1 To AreasCount, 1 To 2)
        
    Dim arg As Range, a As Long, RowsCount As Long, ColumnsCount As Long
    
    ' Map the number of rows and columns per area to an array,
    ' and calculate the maximum number of rows and the number of total columns.
    For Each arg In rg.Areas
        a = a + 1
        rcData(a, 1) = arg.Rows.Count
        If rcData(a, 1) > RowsCount Then RowsCount = rcData(a, 1)
        rcData(a, 2) = arg.Columns.Count
        ColumnsCount = ColumnsCount + rcData(a, 2)
    Next arg
    
    ' Define the resulting array.
    Dim Data() As Variant: ReDim Data(1 To RowsCount, 1 To ColumnsCount)
    a = 0
        
    Dim aData() As Variant, r As Long, c As Long, Col As Long
        
    ' Populate the resulting array.
    For Each arg In rg.Areas
        ' Return the values of each area in a helper array.
        a = a + 1
        If rcData(a, 1) * rcData(a, 2) = 1 Then ' single cell
            ReDim aData(1 To 1, 1 To 1): aData(1, 1) = arg.Value
        Else ' multiple cells
            aData = arg.Value
        End If
        ' Populate the resulting array with values from the helper array.
        For r = 1 To rcData(a, 1)
            For c = 1 To rcData(a, 2)
                Data(r, Col + c) = aData(r, c)
            Next c
        Next r
        Col = Col + c - 1
    Next arg
    
    GetHStackedAreas = Data
    
End Function

A Worksheet Test

Sub Test()
    
    ' Reference the range.
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = Union( _
        ws.Range("A2:B6"), _
        ws.Range("E2:F11"), _
        ws.Range("H2:H8"))
        
    ' Get horizontally stacked areas.
    Dim Data() As Variant: Data = GetHStackedAreas(rg)
    
    ' Return in worksheet.
    ws.Range("J2").Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
    
    ' Return in listbox.
'    With lb
'        .List = Data
'        .ColumnCount = UBound(Data, 2)
'    End With
    
End Sub

Note that in MS365 you could achieve almost the same with the following formula:
```
=IFNA(HSTACK(A2:C6,E2:F11,H2:H8),"")
```
The only difference is that the formula returns empty strings instead of empty cells.

Great work, also nice explained. Though the other contributers also gave some good direction. I will accept this answer as my solution. Works great. Added you function to my code.

CDP1802 · Accepted Answer · 2025-09-13 12:51:42Z

1

You can't assign a non-contiguous range to an array. Copy the values across with a loop and resize the array

Sub demo()

    Dim arList, i As Long, lastrow As Long
    With ActiveSheet
        lastrow = .Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
        arList = .Range("A1:F" & lastrow)
    End With
    
    For i = 1 To lastrow
        arList(i, 3) = arList(i, 5)
        arList(i, 4) = arList(i, 6)
    Next
    ReDim Preserve arList(1 To lastrow, 1 To 4)
      
    With lb
        .List = arList
        .ColumnWidths = "100;200;100;100;100;100"
        .ColumnCount = 6
    End With

End Sub

edited Sep 13 at 12:51

answered Sep 13 at 12:40

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

1 Comment

T.M. Sep 13 at 18:25

lb.ColumnCount = 6 ?

user3598756 · Accepted Answer · 2025-09-14 12:50:47Z

1

you can use the Application.Index() function

With ActiveSheet    
    ' get all the range values in an array
    Dim a As Variant
        a = .Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value     
End With

' choose the columns of the array to be listed
Dim cols As Variant
    cols= Array(1, 2, 5, 6) ' ' keep the 1st, 2nd, 5th and 6th columns

'slice the array and keep the selected columns only
a = Application.Index(a, _
                      Application.Evaluate("row(1:" & UBound(a) & ")"), _
                      cols) 
With lb
    .ColumnCount = UBound(cols) + 1
    .List = a
    .ColumnWidths = "100;200;100;100"
End With

BTW ActiveSheet is the implicit sheet reference, so that you can omit it along with all the leading dots:

' get all the range values in an array
Dim a As Variant
    a = Range("A1:F" & Cells(Rows.Count, "A").End(xlUp).Row).Value

The usage of the Application.Index() function allows for a shorter and, to my opinion, more maintainable code, while it may suffer from two drawbacks:
- it is restricted to a limited number of rows (I think 65536)
- it is slower than any loop approach
Hence the choice of it should be made with respect with the above listed pros an cons

edited Sep 14 at 12:50

answered Sep 14 at 12:43

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

2 Comments

Stephan Sep 15 at 6:12

Thanks for this as well. Tested your solution too and in this case it was working too (and fast). My dataset is only limited. So I can savely use the index solution as well. The dataset wil only hold between 2K en maybe 4K records. And to be honest, I was thinking in this direction myself. So read the whole dataset and then remove the columns not needed but didn't know how I could do this in an efficient way. In my case I have 31 columns and I only need 8 of them. Thx again

T.M. Sep 15 at 9:18

FYI Using a limited dataset you may profit from (further) pecularities of the Application.Index function at my already old post demonstratin not only filtering, but also some surprising restructuring features.:-) @Stephan

Collectives™ on Stack Overflow

How to union multiple ranges and show it in a listbox of a VBA form

4 Answers 4

Comments

Stack Range Areas Horizontally

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Stack Range Areas Horizontally

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related