store a range

[starl]

I need to store the values of a range to be placed at a later time.
What do I store them in? an array? So I need to create a loop to go through the range and place them in there???

thanks

 

[Jim North]

store them for how long? Until the macro ends?

A STATIC variable will hold data while a DIMed variable has a limited life.

Please explain what you are trying to do.

 

[starl]

Well, the procedure will call other procedures, but ultimately it'll be the top procedure and the variables only need to last that long.

As to what I'm doing: I have a group of worksheets which will be deleted - but I need to store some information on those sheets to put in new sheets that will created.
Got a better idea? Sample code???? ANYTHING to make this easier

 

[MrExcel]

Hey Starl,

You can create an array from a range easily:

MyData = Range("A2:Z1000").Value

Then, later you could use:
For i = 1 to UBound(MyData, 1)
For j = 1 to UBound(MyData, 2)
debug.Print MyData(i, j)
Next j
Next i

If you need to store the array, you can store the entire array in a workbook name:
Names.Add Name:="MyName", RefersTo:=MyData

Limits in Excel 2000 or earlier on storing a range in a name: 256 columns and not more than 5461 elements total. In XL2002 or later, the size is limited only by memory.

Credit to Bullen's "Excel 2002 VBA"

Bill

 

[starl]

MyData is of what type variable?