Add a row to a 2d array while preserving the old values

malamutus

New Member
Joined
May 8, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hi All, this is driving me crazy. I have looked all over and tried a ton of things. Hitting a wall.

I am just trying to load in data from an Excel Table to an array. Later on I want to add a new row to this 2d array that was created.

Code:

Dim Arr As Variant
Arr = Worksheets(wsVal).ListObjects(tblVal).DataBodyRange

This works fine and loads in the values of a table that is 1 row by 7 columns

Now I want to simply add a new Row to this array while preserving the old values.

So I try this statement:

ReDim Preserve Arr(1 To 2, 1 To 7) As Variant

This statement gives me a "Subscript out of range" error

This statement works fine:

ReDim Preserve Arr(1 To 1, 1 To 7) As Variant

And it preserves row #1 Array values Arr(1,1) Arr(1,2), etc. But of course it is not doing what I want it to do (which is to add a new row to the Array)

What do I need to do to accomplish this?

Thanks.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,105
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Redim Preserve only allows you to change the last dimension in an array. See here for more.

Provided your array is not huge(>65,000+ rows), it would be possible like this

VBA Code:
Dim Arr As Variant
Arr = Worksheets(wsVal).ListObjects(1).DataBodyRange
Arr = Application.Transpose(Arr)
ReDim Preserve Arr(1 To 7, 1 To 2)
Arr = Application.Transpose(Arr)
 
Solution

malamutus

New Member
Joined
May 8, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the incredibly fast response on this Peter! I tried it and works great. I did not realize you could only use the preserve on the last dimension and also never used the Transpose function before so did not even think about this alternate solution to the problem. No wonder I was challenged. lol There will only be a handful of records in my array so I don't expect the transpose to be processor intensive. Thanks again.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,105
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)

(The range size is not important due to processing resources but because Transpose fails for more than 65536 rows)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,970
Messages
5,656,157
Members
418,283
Latest member
Venkateswara Rao

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
Top