What type of array am I working with when I place specific columns into an array and try pasting them to a new sheet

[JONABRA]

Greetings

I'm fairly new to VBA and finally require some assistance, as I can't seem to find this question on any of the forums

I have a sheet with multiple columns, but only need a select few. I recently came across a method to paste an entire column in an array with a line of code that looks like this : relevantcols(1) = oldsched.Range("cp1:cp" & lrowoldsched) etc. In the example of code I provided below I only need 3 specific columns placed into the array. The issue arises when attempting to paste the entire array back into the worksheet. Am I able to paste all 3 columns with one line of code back into the worksheet?

I tried utilizing the resize function like this:
Worksheets("arrays").Range("m1").Resize(rowcount, colcount).Value = relevantcols

I got the rowcount and colcount using this:

rowcount = UBound(relevantcols(1)) - LBound(relevantcols(1)) + 1
colcount = UBound(relevantcols) - LBound(relevantcols) + 1

however the only way I can paste it back is using a for loop as seen below. What kind of array am I working with here an why can't I paste the entire array in one line of code?

Dim relevantcols() As Variant


Dim tpmt, oldsched As Worksheet
Dim lrow, lrowoldsched, counter, i As Integer


Set tpmt = Worksheets("Third Party & M-Net Merged")
Set oldsched = Worksheets("SSCCatchUpScheduleRpt")

relevantcols(1) = oldsched.Range("cp1:cp" & lrowoldsched)
relevantcols(2) = oldsched.Range("ct1:ct" & lrowoldsched)
relevantcols(3) = oldsched.Range("cs1:cs" & lrowoldsched)


For x = 1 To 6
Worksheets("arrays").Cells(1, x).Resize(rowcount).Value = relevantcols(x)
Next x

when using the watches window the array looks like this relevantcols(1)(1,1) and so on

Much Thanks

 

[Fluff]

What you have is an array of arrays, also known as a jagged array.
You can load the 3 columns into a single 2D array like

Sub Jonabra()
   Dim Ary As Variant
   Dim UsdRws As Long
   
   With Sheets("SSCCatchUpScheduleRpt")
      UsdRws = .Range("CP" & Rows.Count).End(xlUp).Row
      Ary = Application.Index(.Range("CP1:CT" & UsdRws).Value, Evaluate("row(1:" & UsdRws & ")"), Array(1, 5, 4))
   End With
   Sheets("Sheet1").Range("A1").Resize(UsdRws, 3).Value = Ary
End Sub

 

[JONABRA]

Thank you so much for that explanation! I didn't think I'd receive such a quick response. Also from a name I've come across many times while learning to code during lockdown!

 

[Fluff]

You're welcome & thanks for the feedack.

 

[JONABRA]

Hi Fluff or any one reading this, for some reason that line of code doesn't want to work anymore, when I tested it worked but now I'm receiving an error.

Ary = Application.Index(.Range("CP1:CT" & UsdRws).Value, evaluate("row(1:" & UsdRws & ")"), Array(1, 5, 4))

evaluate is highlighted in blue and this error message follows :

"compile error
wrong number of arguments or invalid property assignment "

any ideas, could there be an issue with excel itself?

 

[Fluff]

Do you have any variables or procedures called Evaluate?

 

[JONABRA]

Perfect! I had a sub called evaluate! thanks Fluff that had me scratching my head for a day!

 

[Fluff]

You're welcome & thanks for the feedback.
It's best to avoid using VBA keywords as the names of variables/procedures, for just this reason.