I need to put a 3 column sheet into an array

**TravelByFireworks** · 06-04-2018, 05:31 PM

I have searched through everything I can find and am not really getting anywhere. I have a 3 column sheet with a dynamic named range spanning column A. I have tried this

Please Login or Register  to view this content.

and it works to a point. The problem is that the number of rows changes. I have tried this

Please Login or Register  to view this content.

Which failed because variable LastRow was not allowed in the Array declaration.

Any ideas?

**protonLeah** · 06-04-2018, 06:05 PM

Option1 (if the "mysheetrange" is a dynamic range)

Dim MyArr as Variant
MyArr=Range("MySheetRange").Value

Option 2,

do not DIM MyArr, just use:
ReDim MyArr(1 To Range("mysheetrange").Rows.Count, 1 To Range("Mysheetrange").Columns.Count)

**Trebor76** · 06-04-2018, 06:11 PM

Hi there,

You need to use ReDim:

Please Login or Register  to view this content.

Note I've also changed your integer variables to long as their maximum is 32,767 whereas each Excel tab has 1,048,576 rows.

Regards,

Robert

**Greg M** · 06-04-2018, 06:36 PM

Hi there,

See if the following code does what you need:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

I need to put a 3 column sheet into an array

LinkBack

Thread Tools

Rate This Thread

Display

I need to put a 3 column sheet into an array

Re: I need to put a 3 column sheet into an array

Re: I need to put a 3 column sheet into an array

Re: I need to put a 3 column sheet into an array

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Array to avg rows meeting criteria in one column and single-row (non array) in 2nd column

VBA Change colour of row if content in column matches array in another sheet

[SOLVED] 2 Column Lookup with 6 Column array Col index number is Varies Within Entire Array

VBA UserForm - Contact sheet; Using a ComboBox array to write data to specific column

[SOLVED] New to arrays-Create two column array with different column order to sheet?

index / array return a value from same column in another sheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions