Creating a Matrix of Variable Size in VBA

glshppn

New Member
Joined
Jul 8, 2008
Messages
3
I'm trying to create a matrix of variable size with vba code, but can't seem to get the whole variable size part to work. I could make one that's larger than i need then search through the data until i find a row in the matrix that's full of zero's, but I'd rather just make one that's the size I need it each time. Does anyone know how to make it work? Thanks -Ricky
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
This is one way to load an array with the column values before the first 0.

Code:
Dim myArray as Variant

With Column(1)
   Set myArray = Application.Transpose(.Value)
   For i = 1 to .Rows.Count
       if myArray(i) = 0  Then Exit For
   Next i
   ReDim Preserve myArray(1 to i-1)
End With

Other pre-counting methods would depend strongly on what condition you were testing for.
 
Last edited:
Upvote 0
I know there are only going to be 6 columns, but the number of rows will vary. I have several sheets in an individual excel file and want to copy the data from each sheet into my matrix and then paste it into my original excel file. I want to copy 6 pieces of information from each sheet. I have 200 some excel files to open with a various number of sheets in each file.
 
Upvote 0
yes ... I could just put them in an array and go back and forth between open workbooks, but can't ever seem to get that to work without an error. So a matrix would let me put each sheet's data on one row of the matrix ...
 
Upvote 0
If you are looking for a bulk method to fill one row of a 2D array (with more than one row), I've heard that there are languages (C+(?)) that do that, but VB can't.

If you want to fill a grid of cells in the Summary workbook, you could use something like
Code:
For Each oneWorkbook In sourceWorkbooksCollection
    Set destinationRange = Workbooks("Summary").Sheets(1).Range("A65536").End(xlUp).Resize(1, 6)
    destinationRange.Value = oneWorkbook.Sheets(1).Range("A1:A6").Value
Next oneWorkbook
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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