Is it possible to auto-extend the declared range of a Multi-Dimensional Array?

[bcpelkey]

Hi,

Per a Mr. Excel book: VBA And Macros: Microsoft Excel 2010, I coded the following - which works - but currently requires manual updating of the specified array range in the code when data is added to the bottom of the 2 column table. My question is whether there is a way via code to automatically respecify the range so as to include new data added to the bottom of the table?

Dim Descrip2Array As Variant

Descrip2Array = Worksheets("Description 2 Name").Range("A2:B38")

b = 0
For b = 1 To UBound(Descrip2Array, 1)
If Descrip2Array(b, 1) = CostCenter Then ActiveCell.Value = Descrip2Array(b, 2)
Exit For
End If
Next b

So the range I specified in code extends through Row 38. When we add data past Row 38, is there any way other than manually updating the code to extend the declared range of the array?

Thanks for any help offered.

Brian

 

[pjmorris]

Firstly welcome to the forum, I hope you find what you need here.

Assuming that your data is the last row in the sheet, this might work:

Descrip2Array = Worksheets("Description 2 Name").Range("A2",cells(rows.count,"B").end(xlUp))

 

[pjmorris]

As an additional thought, this works because the array is effectively storing a range which provides access to the data in a number of cells. If it were truly an array you would use Redim to change the size, so in this case you might have:

Dim descriptions() as string
redim descriptions(cells(rows.count,"B").end(xlup).row)

The down side of this is that it won't populate the array for you as in the previous example and you'd need separate code to so.

 

[RoryA]

I'd recommend you simply name the range in Excel then you can just use:

Descrip2Array = Worksheets("Description 2 Name").Range("range_name_here")

without having to worry where the range actually is.

 

[pjmorris]

Hi Rory, to fully answer the query would'nt the OP have to make it a dynamic name?

 

[RoryA]

That would be better certainly, but the question was strictly about having the code automatically recognise the whole range - that would be true even if you manually altered the range name

 

[bcpelkey]

Firstly welcome to the forum, I hope you find what you need here.

Assuming that your data is the last row in the sheet, this might work:

Descrip2Array = Worksheets("Description 2 Name").Range("A2",cells(rows.count,"B").end(xlUp))

Hi Peter,

I copied your suggested change in, but I got this error:

"Application-defined or object-defined error".  The code stopped right on that line.

Maybe I should use your "cells(rows.count,"B").end(xlUp)" outside of the array statement to create a row count value for a column B variable and then incorporate that variable into the array statement?

Brian

 

[pjmorris]

As best as I was able to test it it worked for me. Actually I did consider using it outside the statement to create the array count. It might be worth investigating (us the debugging facility) to see if you can break down the statement and find out why its not working for you. It might be I've not typed things exactly the same as your macro requires. One possible cause could be because the sheet name is incorrect.