Dim rng As Range
Dim ary As Variant
Set rng = Range("A1:C10")
ary = rng.Value

How to loop through array and read/modify/delete elements

I've tried variations on the code below, but no joy so far...

For i = 1 to ubound (ary(1))
For j = 1 to Ubound(ary(2))
msgbox ary(i,j)
next
next

Try this

Dim rng As Range
Dim ary As Variant
Set rng = ActiveSheet.Range("A1:C10")
For Each c In rng
MsgBox c.Value & " " & c.Address
Next

Obviously instaed of the message box it's possible to change the value of
each object in the range

Mike

"David" wrote:

Dim rng As Range
Dim ary As Variant
Set rng = Range("A1:C10")
ary = rng.Value

How to loop through array and read/modify/delete elements

I've tried variations on the code below, but no joy so far...

For i = 1 to ubound (ary(1))
For j = 1 to Ubound(ary(2))
msgbox ary(i,j)
next
next

David,

You are not using UBound correctly:

For i = 1 To UBound(ary, 1)
For j = 1 To UBound(ary, 2)
MsgBox ary(i, j)
Next
Next

HTH,
Bernie
MS Excel MVP


"David" wrote in message
...
Dim rng As Range
Dim ary As Variant
Set rng = Range("A1:C10")
ary = rng.Value

How to loop through array and read/modify/delete elements

I've tried variations on the code below, but no joy so far...

For i = 1 to ubound (ary(1))
For j = 1 to Ubound(ary(2))
msgbox ary(i,j)
next
next

You have the syntax wrong for your UBound function calls in the two For
statements...

For I = 1 To UBound(ary, 1)
For J = 1 To UBound(ary, 2)

Rick


"David" wrote in message
...
Dim rng As Range
Dim ary As Variant
Set rng = Range("A1:C10")
ary = rng.Value

How to loop through array and read/modify/delete elements

I've tried variations on the code below, but no joy so far...

For i = 1 to ubound (ary(1))
For j = 1 to Ubound(ary(2))
msgbox ary(i,j)
next
next

And I'd use lbound, too:

For I = lbound(ary,1) To UBound(ary, 1)
For J = lbound(ary,2) To UBound(ary, 2)

Why remember if/when the array is 0-based or 1-based or something else based.

David wrote:

Dim rng As Range
Dim ary As Variant
Set rng = Range("A1:C10")
ary = rng.Value

How to loop through array and read/modify/delete elements

I've tried variations on the code below, but no joy so far...

For i = 1 to ubound (ary(1))
For j = 1 to Ubound(ary(2))
msgbox ary(i,j)
next
next

--

Dave Peterson