Better Understanding of 2D Array Writing [VBA]

[DataBlake]

Sub aanewWPcode()
    Dim ary As Variant
    Dim i As Long
    

With ActiveSheet
    ary = .Range("A1").CurrentRegion.Value2


End With
End Sub

So i have this macro that will assign an entire sheet as an array
I'm trying to copy the entire 2nd column to the 1st column so

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Blank
[/TD]
[TD]Part Number
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]456
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]789
[/TD]
[/TR]
</tbody>[/TABLE]

becomes
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Blank
[/TD]
[TD]Part Number
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD]456
[/TD]
[TD]456
[/TD]
[/TR]
[TR]
[TD]789
[/TD]
[TD]789
[/TD]
[/TR]
</tbody>[/TABLE]

i don't want to use i = 2 to ubound
i want to write this using the range.value = ary method, but i'm not sure how to write that line.

 

[DataBlake]

ah.....i obviously didnt spend enough time thinking about this

Sub aanewWPcode()
    Dim ary As Variant
    Dim i As Long
    Dim lastRow As Long
    

With ActiveSheet
ary = .Range("A1").CurrentRegion.Value2

For i = 2 To UBound(ary)
ary(i, 1) = ary(i, 2)
Next i

.Range("A1").Resize(UBound(ary), 1).Value = ary

End With
End Sub

do you think theres a better way to write this though?

 

[Fluff]

What you have done is probably the safest way but you could also use

.Range("A1").Resize(UBound(ary), 1).Value = Application.Index(ary, 0, 2)

but you may have problems with large amounts of data.
I can't remember the limit off hand, but I think it's about 32000 rows.

 

[DataBlake]

but I think it's about 32000 rows.

yeah that won't work with what i have.
the plan now is to group all of my data (ranging from 3000-100,000+ rows of data) together in an array to make changes to all of it at once rather than segmenting the data and calling on non array macros.
any reason why that would cause any issues?

its seeming like i can make this entire process which takes well over 30 min for my smallest dataset to literal minutes.
I.E right now i have it to where two arrays are "talking to each other" and making changes to the data this way

Sub aanewWPcode()
    Dim ary As Variant
    Dim i As Long
    Dim lastRow As Long
    

With ActiveSheet

' assign two arrays; one for reference and one for printing values
ary = .Range("A1").CurrentRegion.Value2
ary2 = .Range("A1").CurrentRegion.Value2

' transfer correct data to proper columns using the two arrays
For i = 2 To UBound(ary)
ary2(i, 1) = ary(i, 2)
ary2(i, 2) = ary(i, 38)
ary2(i, 3) = ary(i, 41)
ary2(i, 4) = ary(i, 69)
ary2(i, 5) = ary(i, 8)
ary2(i, 6) = ary(i, 9)
ary2(i, 7) = ary(i, 4)
ary2(i, 8) = ary(i, 13)
ary2(i, 9) = ary(i, 14)
ary2(i, 10) = ary(i, 11)
ary2(i, 11) = ary(i, 12)

' if statement for determing map or retail pricing
If ary(i, 7) > 0 Then
ary2(i, 12) = ary(i, 7)
ElseIf ary(i, 6) > 0 Then
ary2(i, 12) = ary(i, 6)
Else
ary2(i, 12) = "err"
End If

ary2(i, 13) = ary(i, 19)
ary2(i, 14) = ary(i, 36)
ary2(i, 15) = "img"
ary2(i, 16) = "title"
ary2(i, 17) = "desc"
ary2(i, 18) = "qty"
ary2(i, 19) = ary(i, 15)
Next i

' print the array to the sheet
.Range("A1").Resize(UBound(ary2), 19).Value = ary2
End With



End Sub

would this be problematic in any way if there were 100,000 rows and 19 columns of data?

 

[Fluff]

The only problem that I can think of offhand is it might be more data than your computer can handle so you may get an something along the lines of "Out of Memory" or "Out of Stack space"
The only way to know, is to try it.

 

[DataBlake]

The only way to know, is to try it.

Excellent
if that happens i'll cry to my IT department for more RAM

 

[Fluff]

You're welcome & thanks for the feedback