VBA - Question on Array

Question

When using Excel VBA to create macros, is it possible to create a single 3-dimensional array instead of two 2-dimensional arrays then copy a selected range.value into the 3-dimensional array by only selecting the 1^st^ array dimension instead of copying a selected range.value into each of the 2 dimensional arrays?

Instead of:

• Dim ShtArray1(3,50) as Variant
• Dim ShtArray2(3,50) as Variant
• ShtArray1 = Range(“a1:c50”).Value
• ShtArray2 = Range(“d1:f50”).Value

Use:

• Dim SheetArray(2,3,50) as Variant
• SheetArray(1) = Range(“a1:c50”).Value
• SheetArray(2) = Range(“d1:f50”).Value

<Mod's note - Changed the title for better clarity >

Answer 1

Hi,

take a look here, maybe could help..

http://stackoverflow.com/questions/15007293/compare-two-sheets-using-arrays

Answer 2

I realize that I don't need a 2-dimensional array to gather data from a range. However, the total application that I have written is comparing the data in each column in each row of the 2 worksheets to see if rows are missing so I need to use in-memory arrays to compare the data instead of comparing cells directly in the worksheets to save enormous amounts of time on extremely large worksheets. I simply want to know if it is possible to move a worksheet to a 3-dimensional array, where the 1st dimension is the worksheet number, with a single statement instead of stepping through each row/column, as I can do with a 2-dimensional array as follows:

     Option Base 1

     Dim ShtArray(1000,20) as Variant

              .

              .    

     ShtArray = Range("A1:T1000")

Answer 3

i deleted my answers.

I think that James, has answered to your query.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[edit]

just a note

you don't need a 2D array in order to gather data from a range

sample

data in range A1:C10

try

Sub macro_01()

'Jan 12, 2016

ReDim v1(1 To 30)

v1 = Range("A1:C10")

Sheets.Add

t = 1

For Each vv In v1

Cells(t, 1) = vv

t = t + 1

Next

End Sub

Answer 4

I can move an entire worksheet to a 2-dimensional array with a single statement without having to step through the

For...Next loops for each row/column. I would like to move 2 worksheets of the same size to a 3-dimensional array where the 1st dimension is the worksheet # also with a single statement without having to step through the For...Next loops for each worksheet/row/column...