Excel VBA Array Ranges for a loop

Question

I'm trying to get a loop function to refer to a range of cells, rather than typing in the worksheet names individually.

I've got this code that works:

Sub LoopTest()
'
' Macro4 Macro
'

'
' Loop Test

Dim SheetsArray As Variant
Dim i As Integer
SheetsArray = Array("Bun1", "Bun2")

For i = LBound(SheetsArray) To UBound(SheetsArray)
    With Worksheets(SheetsArray(i)).Select
        Range("A2:G60").Select
        Selection.ClearContents
    End With
Next i

End Sub

But then this code doesn't when I try and get it to refer to a range instead:

Sub LoopTest()
'
'
' Loop Test

Dim Arr As Variant
Dim i As Integer
Dim rng As Range

Set rng = Worksheets("Names").Range("A2:A3")

Arr = rng

For i = LBound(Arr) To UBound(Arr)
    With Worksheets(Arr(i)).Select
        Range("A2:G60").Select
        Selection.ClearContents
    End With
Next i

End Sub

I'm getting "Subscript out of range" error when I try and use it, however the worksheets all exist (the range has the same names as the first code). Debug is highlighting the

WIth Worksheets(Arr(i)).Select

line. Any help would be greatly appreciated!

-Cr1kk0

Rory · Accepted Answer · 2015-03-09 09:53:48Z

3

You always get a 2 dimensional array when you assign a range to a variant so you need to specify both dimensions. There's also no need to select anything here:

For i = LBound(Arr, 1) To UBound(Arr, 1)
    Worksheets(Arr(i, 1)).Range("A2:G60").ClearContents
Next i

answered Mar 9, 2015 at 9:53

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cr1kk0 Over a year ago

Worked flawlessly. Thank you! Sorry, I don't have the reputation yet to upvote your answer.

Waleed Over a year ago

You answer saved my life .

Collectives™ on Stack Overflow

Excel VBA Array Ranges for a loop

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related