Use VBA to display select range values in Excel listbox

Question

I have tried without success to display a non-contiguous range of row cells into a 2-column listbox in Excel 2016. The lastest suggestion I'm using is to move range values into an array and use the array to write to the listbox.
I used the FindAll function to search through a large name range to extract rows that met the string criteria. I end up with a 28 row x 4 column non-contiguous range ( eg: A3:D3, A7:D7, A15:D15, A25:D25, ...) .

My issue is at the top of the 2nd loop cycle:

Dim result As Range, item As Range
Dim Arr() As String, sStr As String
sStr = "A3:D3","A7:D7","A15:D15","A25:D25"
Set result = Range(sStr)
i = 0
For Each item In result
   ReDim Preserve Arr(i+1, 4)
   Arr(i , 1) = item(1, 1).Value
   Arr(i , 2) = item(1, 2).Value
   Arr(i , 3) = item(1, 3).Value
   Arr(i , 4) = item(1, 4).Value
   i = i + 1
Next

The 1st loop interation works with correct values passed from item -> Arr, but at the 2nd loop all goes awry. Looking at the watch list the item.values are not correct and loop bombs at ReDim statement with a runtime error '9': Subscript out of range. It must be the range index and/or ReDim statement.

EvR EvR · Accepted Answer · 2018-10-22T09:25:32

Does this work for you?

Dim result As Range, item As Range
Dim Arr() As String, sStr As String
sStr = "A3:D3,A7:D7,A15:D15,A25:D25"

Set result = Range(sStr)
ReDim Arr(result.Areas.Count, 4)
i = 0
For Each item In result.Areas
   Arr(i, 1) = item(1, 1).Value
   Arr(i, 2) = item(1, 2).Value
   Arr(i, 3) = item(1, 3).Value
   Arr(i, 4) = item(1, 4).Value
   i = i + 1
Next

Use VBA to display select range values in Excel listbox

1 Answers