Excel 2016 - VBA: Transform values in column into array

[alpha2007]

Hello!

Unfortunately, I have not worked with VBA arrays so far, and thus I don't know of how to transfer values in a column to an array. I hope someone can help me find a solution for it

I have a variable list of URLs in column (("A1:A" & lastRow)) of worksheet "sheet1" wherêby
lastRow = .Cells(.rows.Count, "A").End(xlUp).Row

The array I need to use in a VBA Sub is as following:

urls = Array("https://www.bizbuysell.com/Business-Opportunity/covid-friendly-commercial-cleaning-est-30-years-100k-net/1753433/?d=L2Zsb3JpZGEvaGlsbHNib3JvdWdoLWNvdW50eS1idXNpbmVzc2VzLWZvci1zYWxlLzI/cT1hVEk5T0RFc01qQXNNekFzTnpnbWJtRndQV1UlM0Q=", _
"https://www.bizbuysell.com/Business-Opportunity/Established-Cleaning-Business-Tampa-St-Pete/1849521/?utm_source=bizbuysell&utm_medium=emailsite&utm_campaign=shtmlbot&utm_content=headline")

In the above example VBA array urls, there are only two URLs and I need all URLs of column A in "sheet1" to be in that specific array.

Thank you for helping me!

 

[kweaver]

I think this is one way provided that A1 has a header. If not, adjust accordingly:

Sub Alpha2007()
Dim lr As Long, url() As String, i As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row - 2
ReDim url(lr)
For i = 0 To lr
 url(i) = Sheets("Sheet1").Cells(i + 2, "A")
Next i
End Sub

 

[Marc L]

Hi,​

​

with a single codeline : urls = [Sheet1!A1].CurrentRegion.Columns(1).Value2 …​

 

[alpha2007]

Thank you kweaver and Marc L

Question for Marc L:

urls = [Sheet1!A1].CurrentRegion.Columns(1).Value2

Is urls an array as in my example?

I ask because each individual url in the array urls is needed for the loop in following code, and the loop does not work

    Dim url As Long, results() As Variant
 
    ReDim results(1 To UBound(urls) + 1, 1 To 21) 

    With http
 
        For url = LBound(urls) To UBound(urls)   'loop url list
 
            .Open "Get", urls(url), False
            .setRequestHeader "User-Agent", "Mozilla/5.0"
            .send
 
            html.body.innerHTML = .responseText
 
            Dim currentDetailedInformation As Scripting.Dictionary
 
            Set currentDetailedInformation = GetCurrentDetailedInfo(html)
 
            AddCurrentDetailedInfoToResults results, currentDetailedInformation, url, urls(url), html
        Next
 
    End With

Thank you so much for looking into it again!

 

[kweaver]

Each element of url is part of the array. Try checking url(0), or url(1), etc.

 

[Marc L]

Question for Marc L:

As the source data is a column so the first element of the array is urls(1, 1), the second is urls(2, 1), … as you can check in the Locals VBE window !​

 

[alpha2007]

Thank you - I got it!