ReDim Preserve with multidimensional array in Excel VBA

Question

I can get this to work but am not sure if this is the correct or the most efficient way of doing this.

Details: Looping through 151 rows then assigning column A and B only of those rows to a two dimensional array based on criteria in column C. With the criteria only 114 of the 151 rows are needed in the array.

I know that with ReDim Preserve you can only resize the last array dimension and you can't change the number of dimensions at all. So I have sized the rows in the array to be the total 151 rows using the LRow variable but the actual rows I only need in the array is in variable ValidRow so it seems that (151-114) = 37 superfluous rows are in the array as a result of the ReDim Preserve line. I would like to make the array only as big as it needs to be which is 114 rows not 151 but not sure if this is possible see code below and any help much appreciated as I am new to arrays and have spent the best part of two days looking at this. Note: Columns are a constant no issue with them but rows vary.

Sub FillArray2()

Dim Data() As Variant
Dim ValidRow, r, LRow As Integer

Sheets("Contract_BR_CONMaster").Select
LRow = Range("A1").End(xlDown).Row '151 total rows

Erase Data()

For r = 2 To LRow
 If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
  ValidRow = ValidRow + 1
  ReDim Preserve Data(1 To LRow, 1 To 2)
  Data(ValidRow, 1) = Range("A" & r).Value 'fills the array with col A
  Data(ValidRow, 2) = Range("B" & r).Value 'fills the array with col B
 End If

Next r

ActiveWorkbook.Worksheets("Test").Range("A2:B" & ValidRow + 1) = Data() 'assign after     loop has run through all data and assessed it

End Sub

Can you oversize the array, fill it with what you need and then use ReDim Preserve once only to reduce it to the required size? — user857521
– user857521, Commented Aug 2, 2014 at 12:51
Thank you for your reply. If I size the array at dim level with constants and then put in a ReDim below this I get error that "Array already dimensioned". Second I do not know how many rows the array needs to be until the For/Next loop is complete using the ValidRow variable. But I think I just found a way with transpose will answer my question. This seems to work. — Derek
– Derek, Commented Aug 3, 2014 at 1:18

Derek · Accepted Answer · 2014-08-03 01:34:03Z

I seemed to have got this to work by using transposition where the rows and cols are swapped around and still using ReDim Preserve then transposing at the end when assigning to a range. This way the array is exactly the size it needs to be with no blank cells.

Sub FillArray3() 'Option 3 works using transposition where row and cols are swapped then swapped back at the end upon assignment to the range with no blank cells as array is sized incrementally via the For/Next loop

Dim Data() As Variant
Dim ValidRow, r, LRow As Integer

Sheets("Contract_BR_CONMaster").Select
LRow = Range("A1").End(xlDown).Row '151 total rows

Erase Data()

For r = 2 To LRow
 If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
  ValidRow = ValidRow + 1
  ReDim Preserve Data(1 To 2, 1 To ValidRow) 'can change the size of only the last dimension if you use Preserve so swapped rows and cols around
  Data(1, ValidRow) = Range("A" & r).Value 'fills the array with col A
  Data(2, ValidRow) = Range("B" & r).Value 'fills the array with col B
 End If

Next r

ActiveWorkbook.Worksheets("Test").Range("A2:B" & ValidRow + 1) = Application.Transpose(Data) 'swap rows and cols back

End Sub

Pieter Geerkens · Accepted Answer · 2014-08-02 13:38:15Z

3

Note also that the internal VBA implementation of REDIM is not guaranteeing to release the storage when it is sized down. It would be a common choice in such an implementation to not physically reduce the storage until the size dropped to less than half the input size.

Have you considered creating a type-safe collection class to store this information instead of an array? In it's most basic form (for a storage type of Integer) it would look be a Class Module like this:

Option Explicit

Private mData As Collection

Public Sub Add(Key As String, Data As Integer)
    mData.Add Key, Data
End Sub

Public Property Get Count() As Integer
    Count = mData.Count
End Property

Public Function Item(Index As Variant) As Integer
    Item = mData.Item(Index)
End Function

Public Sub Remove(Item As Integer)
    mData.Remove Item
End Sub


Private Sub Class_Initialize()
    Set mData = New Collection
End Sub

A particular advantage of this implementation is that the sizing logic is completely removed from the client code, as it should be.

Note that the Data type stored by such a patter can be any type supported by VBA, including an Array or another Class.

edited Aug 2, 2014 at 13:38

answered Aug 2, 2014 at 13:32

Pieter Geerkens

11.9k2 gold badges35 silver badges54 bronze badges

3 Comments

user857521 Over a year ago

out of curiosity, do you have any examples where Redim won't release memory? I just tried redim for thousands of iterations and memory seemed to reduce linearly using just a 2d variant array.

Pieter Geerkens Over a year ago

@ooo: No, I have no specific examples of this. However the implementation details are undocumented (as best I can determine) so one cannot make assumptions on behaviour that could be relied upon, particularly between EXCEL versions. Over time I expect VBA to be reimplemented in DOT NET, with VBA Arrays being implemented as DOT NET List objects where sizing would be done as I suggest in the answer.

Derek Over a year ago

Thanks for the above. Never used Type Safe in Class Module but I will take this away and try and learn how it works many thanks.

Derek · Accepted Answer · 2014-08-07 10:57:14Z

Two more ways of doing this. FillArray4 - Initial array is created too large but second part of code moves this to a new array using a double loop which creates the array to be the exact size it needs to be.

Sub FillArray4()

Dim Data() As Variant, Data2() As Variant
Dim ValidRow As Integer, r As Integer, lRow As Integer

Sheets("Contract_BR_CONMaster").Select
lRow = Range("A1").End(xlDown).Row '151 total rows

'Part I - array is bigger than it has to be
Erase Data()

For r = 2 To lRow
 If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
  ValidRow = ValidRow + 1 'this is the size the array needs to be 114 rows
  ReDim Preserve Data(1 To lRow, 1 To 2) 'but makes array to be 151 rows as based on lrow not ValidRow as cannot dynamically resize 1st dim of array when using preserve
  Data(ValidRow, 1) = Range("A" & r).Value 'fills the array with col A
  Data(ValidRow, 2) = Range("B" & r).Value 'fills the array with col B
 End If
Next r

'Part II
'move data from Data() array that is too big to new array Data2() that is perfectly sized as it uses ValidRow instead of lrow
Erase Data2()

For i = LBound(Data, 1) To UBound(Data, 1) 'Rows
For j = LBound(Data, 2) To UBound(Data, 2) 'Cols
 If Not IsEmpty(Data(i, j)) Then
  ReDim Preserve Data2(1 To ValidRow, 1 To 2)
  Data2(i, j) = Data(i, j) 'fills the new array with data from original array but only non blank dims; Data2(i,j) is not one particular row or col its an intersection in the array
  'as opposed to part one where you fill the initial array with data from cols A and B using seperate lines for each col
 End If

Next
Next
ActiveWorkbook.Worksheets("Test").Range("A2:B" & ValidRow + 1) = Data2() 'assign data from new array to worksheet

End Sub

Sub FillArray5 - Much simpler and my preferred option as you only create one array. Initial loop determines the size the array needs to be and then second loop uses this to create array and store data. Note only two cols in both cases. Issue I had in this scenario was creating 2D array where rows varied. That's it for me time to go to the tropics for a well earned holiday!

Sub FillArray5()

Dim Data() As Variant
Dim ValidRow As Integer, r As Integer, lRow As Integer, DimCount As Integer,  RemSpaceInArr As Integer

Sheets("Contract_BR_CONMaster").Select
lRow = Range("A1").End(xlDown).Row

Erase Data()

For r = 2 To lRow
 If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
  ValidRow = ValidRow + 1 'this is the size the array needs to be 114 rows
 End If
Next r

DimCount = 0 'reset
 For r = 2 To lRow
  If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
   ReDim Preserve Data(1 To ValidRow, 1 To 2) 'makes array exact size 114 rows using ValidRow from first loop above
   DimCount = DimCount + 1 'need this otherwise ValidRow starts the dim at 114 but needs to start at 1 and increment to max of ValidRow
   Data(DimCount, 1) = Range("A" & r).Value 'fills the array with col A
   Data(DimCount, 2) = Range("B" & r).Value 'fills the array with col B
  End If
 Next r
 RemSpaceInArr = ValidRow - DimCount 'just a check it should be 0

ActiveWorkbook.Worksheets("Test").Range("A2:B" & ValidRow + 1) = Data() 'assign data from array to worksheet

End Sub

Pelton · Accepted Answer · 2023-03-17 08:06:39Z

In my case, I transform usedrange to an variant array for speed reasons (complex calculations in VBA). Indeed, if I wish to add rows: no (simple) way. Hence, IF I KNOW IN ADVANCE, how many rows I will probably add at most, I simply add the number of expected rows to the initial range to transfer. Here we go:

Sub Add_max_50_Rows_via_variant_array()
Dim Ca As Variant
Dim Ra As Range
'assume I wish to add max. 50 columns
Set Ra = ActiveSheet.Range([a1], Cells(ActiveSheet.UsedRange.Rows.Count + 50, ActiveSheet.UsedRange.Columns.Count))
Ca = Ra
'in the example: Assume the initial sheet has 8 rows:
Debug.Print Ca(8, 4) 'example
Ca(9, 1) = 991 'the ninth row is available in the array
Ra = Ca 'and will be reported back to the initial range, of course.
Debug.Print ActiveSheet.UsedRange.Rows.Count '9 - Usedrange is extended to 
    '9 rows only, which is ok.

End Sub

Collectives™ on Stack Overflow

ReDim Preserve with multidimensional array in Excel VBA

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related