Excel VBA fill array with only limited number of columns

[eggem01]

I want to fill an array with only a limited number of columns (6 columns) instead of filling the array with the entire worksheet-data (62 columns an thousands of rows). There is no real need to fill the array with more than just these columns.

The code starting with an apostrophy works just fine, i.e. filling the array with the entire worksheet.
Filling the array with only the 6 specific columns renders a compiling error. Wrong number of arguments or invalid property(error 450).
https://docs.microsoft.com/nl-nl/office ... 6rd%3Dtrue

I have been looking for a solution but can't seem to find one. I am not sure but could f.i. adding column by column with redim preserve help here. I have never used that before. Or is there a different way to fill the array in this case.

The relevant code is:

Code: Select all

'Hier de laatste rij opzoeken
Dim apv_laatste_rij As Long

With sh_apv_data
    apv_laatste_rij = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'De array dynamisch dimmen
Dim apv_data_array() As Variant
'ReDim apv_data_array(1 To apv_laatste_rij, 1 To 62)
ReDim apv_data_array(1 To apv_laatste_rij, 1 To 6)

'De array vullen met data
'apv_data_array = sh_apv_data.Range("A1:BJ" & apv_laatste_rij)

apv_data_array = sh_apv_data.Range("C1:C" & apv_laatste_rij, "G1:G" & apv_laatste_rij, _
"N1:N" & apv_laatste_rij, "V1:V" & apv_laatste_rij, "Y1:Y" & apv_laatste_rij, "Z1:Z" & apv_laatste_rij)

[HansV]

The lines

apv_data_array = sh_apv_data.Range("C1:C" & apv_laatste_rij, "G1:G" & apv_laatste_rij, _
"N1:N" & apv_laatste_rij, "V1:V" & apv_laatste_rij, "Y1:Y" & apv_laatste_rij, "Z1:Z" & apv_laatste_rij)

should be

apv_data_array = sh_apv_data.Range("C1:C" & apv_laatste_rij & ",G1:G" & apv_laatste_rij & _
",N1:N" & apv_laatste_rij & ",V1:V" & apv_laatste_rij & ",Y1:Y" & apv_laatste_rij & ",Z1:Z" & apv_laatste_rij)

That will remove the error message, but it won't work - apv_data_array will only contain the first of the columns instead of all 6.

You can do it like this:

Code: Select all

'Hier de laatste rij opzoeken
Dim apv_laatste_rij As Long

With sh_apv_data
    apv_laatste_rij = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'De array dynamisch dimmen
ReDim apv_data_array(1 To apv_laatste_rij, 1 To 6)

'De array vullen met data
Dim r As Long
Dim c As Variant
Dim n As Long

For Each c In Array(3, 7, 14, 22, 25, 26)
    n = n + 1
    For r = 1 To apv_laatste_rij
        apv_data_array(r, n) = sh_apv_data.Cells(r, c).Value
    Next r
Next c

[eggem01]

Bedankt Hans. Dit werkt prima. Was ik in elk geval nooit opgekomen.

[Doc.AElstein]

&%$§’! Hier is een andere manier om het te proberen , OekyDoeky
arrOut() = Application.Index(Ws.Cells, rws(), clms())

Code: Select all

Sub OekyDoeky()
'
'Hier de laatste rij opzoeken
Dim apv_laatste_rij As Long
With sh_apv_data
    apv_laatste_rij = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
                ''De array dynamisch dimmen
                'ReDim apv_data_array(1 To apv_laatste_rij, 1 To 6)
Dim apv_data_array() As Variant
 Let apv_data_array() = Application.Index(sh_apv_data.Cells, Evaluate("row(1:" & apv_laatste_rij & ")"), Array(3, 7, 14, 22, 25, 26))
' Of
Dim c() As Variant
 Let c() = Array(3, 7, 14, 22, 25, 26)
 Let apv_data_array() = Application.Index(sh_apv_data.Cells, Evaluate("row(1:" & apv_laatste_rij & ")"), c())







'De array vullen met data
'Dim r As Long
'Dim c As Variant
'Dim n As Long
'
'For Each c In Array(3, 7, 14, 22, 25, 26)
'    n = n + 1
'    For r = 1 To apv_laatste_rij
'        apv_data_array(r, n) = sh_apv_data.Cells(r, c).Value
'    Next r
'Next c
'
'
End Sub

Groeten uit Hof, Beieren
Alan
_.________________________________-

Edit:
I added a small explanation on the file, OekyDoeky.xlsm.
It is just a shortened simplified explanation, not a full precise theoretical one!
I first came across this way of doing it from apo and snb, ( https://www.snb-vba.eu/VBA_Arrays.html" onclick="window.open(this.href);return false; ) , but the explanations I made up myself!

Ref
' https://www.excelforum.com/excel-new-us ... ost4571172" onclick="window.open(this.href);return false;
http://www.excelfox.com/forum/showthrea ... nd-VLookUp" onclick="window.open(this.href);return false;
https://www.snb-vba.eu/VBA_Arrays.html" onclick="window.open(this.href);return false; , https://www.snb-vba.eu/index.html" onclick="window.open(this.href);return false;