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

New Member
Joined
Mar 15, 2019
Messages
19
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:
Code:
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?

VBA 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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
VBA Code:
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
 
Upvote 0
Solution
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!
 
Upvote 0
You're welcome & thanks for the feedack.
 
Upvote 0
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?
 
Upvote 0
Do you have any variables or procedures called Evaluate?
 
Upvote 0
Perfect! I had a sub called evaluate! thanks Fluff that had me scratching my head for a day!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top