Cannot create the loop

Envil

New Member
Hi All,

I'd like to ask you for advise. I've got the following code which works properly but seems to look perfect to make a loop out of it. Unfortunately I cannot find a way how to do it.

Code:
``````Rng1 = Range("J10")
Rng2 = Range("D18:D30")
Rng3 = Range("K18:K24")
Rng4 = Range("D55:D68")
Rng5 = Range("L55:L68")

Rng1.Copy Rng1.Offset(, 1)
Rng1.ClearContents
Rng2.Copy Rng2.Offset(, 1)
Rng2.ClearContents
Rng3.Copy Rng3.Offset(, 1)
Rng3.ClearContents
Rng4.Copy Rng4.Offset(, 1)
Rng4.ClearContents
Rng5.Copy Rng5.Offset(, 1)
Rng5.ClearContents``````

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Code:
``````Dim rng(5) As Range

Set rng(1) = Range("J10")
Set rng(2) = Range("D18:D30")
Set rng(3) = Range("K18:K24")
Set rng(4) = Range("D55:D68")
Set rng(5) = Range("L55:L68")

For x = 1 To 5
rng(x).copy rng(x).Offset(, 1)
rng(x).ClearContents
Next x``````

NeonRedSharpie - thank you very much for the your help! The loop works perfect. I was very close to this solution however my understanding of variable declaration in this case was wrong. I was trying to do the following:
Code:
``````Dim rng(1) As Range
Dim rng(2) As Range
Dim rng(3) As Range
Dim rng(4) As Range
Dim rng(5) As Range``````

Replies
8
Views
435
Replies
0
Views
485
Replies
0
Views
355
Replies
6
Views
412
Replies
11
Views
2K

1,196,251
Messages
6,014,249
Members
441,808
Latest member
xplainer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back