Can I access a range using index numbers using the format Cells(6, 1) etc

[MrTeeny]

I've been doing some stuff by reading a range into memory and then looping thru it as an array and wondering if I can access ranges within the array to delete or simply stick into another array

So I'd simply declare my array then get the data by say

MyArray=Range("A1:C50").Value

Then access individual data within there as so

MyArray(1,1) for A1 data etc

How would I access say the equivalent data for the Range("A1:B5").Value and could I also overwrite that block en masse i.e, just set that portion of the array to blank cells in a similar way to just Range("A1:B5").Value="" if I was doing it on the sheet and not the array.


Thanks

 

[StephenCrump]

The advantage of using VBA arrays is that they are fast, so it's usually no big deal to loop through the array, setting values individually.

There might be a non-looping alternative, but that depends on what do you intend doing with MyArray once you've cleared part of it?

... wondering if I can ... simply stick into another array

You can use the Index function to extract part of a VBA array, e.g.

lStartRow = 1
lEndRow = 5
lStartCol = 1
lEndCol = 2

v1 = Range("A1:C20").Value2

'Sub-array
v2 = Application.Index(v1, Evaluate("=Row(" & lStartRow & ":" & lEndRow & ")"), Evaluate("=Transpose(Row(" & lStartCol & ":" & lEndCol & "))"))
Range("E1").Resize(UBound(v2), UBound(v2, 2)).Value = v2

 

[MrTeeny]

Thanks I was hoping they'd be some simple syntax like MyArray((1,1,),(3,3))=""or similar