Better Understanding of 2D Array Writing [VBA]

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
Code:
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

Blank
Part Number
123
456
789

<tbody>
</tbody>

becomes
Blank
Part Number
123
123
456
456
789
789

<tbody>
</tbody>

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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
ah.....i obviously didnt spend enough time thinking about this
Code:
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?
 
Upvote 0
What you have done is probably the safest way but you could also use
Code:
.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.
 
Upvote 0
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

Code:
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?
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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