Ask a new question

Range from 2 Ranges for Listbox

I have successfully used code (extract) such as below to populate a listbox:

gCountryListArr6 = Sheets("Risk Register").ListObjects("Risk_Register").ListColumns(7).DataBodyRange.Value

I would like a listbox that lists the combination of two ranges, eg:

rng1 = Sheets("Risk Register").ListObjects("Risk_Register").ListColumns(7).DataBodyRange.Value

rng2 = Sheets("Risk Register").ListObjects("Risk_Register").ListColumns(8).DataBodyRange.Value

What is the correct code to combine these two ranges so that my listbox will contain the contents of both column 7 and column 8?

Note I want this in a single column in the listbox, not 2.

Thanks

Answer
Answer

Your original example had two columns in the same table, so I had no reason to provide for another scenario. This should work for disparate ranges:

    rng1 = Sheets("Risk Register").ListObjects("Risk_Register").ListColumns(7).DataBodyRange.Value
    rng2 = Sheets("Operations and Activities").Range("E51:E60").Value
 
    Dim i As Long
    Dim n1 As Long
    Dim n2 As Long
    n1 = UBound(rng1, 1)
    n2 = UBound(rng2, 1)
    ReDim gCountryListArr6(n1 + n2, 1 To 1)
    For i = 1 To n1
        gCountryListArr6(i, 1) = rng1(i, 1)
    Next i
    For i = 1 To n2
        gCountryListArr6(i + n1, 1) = rng2(i, 1)
    Next i

---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 41 Applies to: