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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

JONABRA

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedack.
 

JONABRA

New Member
Joined
Mar 15, 2019
Messages
19

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
Do you have any variables or procedures called Evaluate?
 

JONABRA

New Member
Joined
Mar 15, 2019
Messages
19
Perfect! I had a sub called evaluate! thanks Fluff that had me scratching my head for a day!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,011
Messages
5,575,541
Members
412,676
Latest member
Otterbox
Top