For Next issue with repeated procedure

I have the procedure below. Basically I don't want to have
interminable lines of code when all I am doing is repeating the same
procedure on a differnet range. How can I write the procedure that it
loops through the procedure with the 6 different ranges

   rng1 = "A1:A9"
   rng2 = "A11:A20"
   Rng3 = "A30:A34"
   Rng4 = "A40:A48"
   Rng5 = "A50:A59"
   Rng6 = "A65:A69"


   n = 6

   For i = 1 To n

   ActiveCell.Range(rng(i)).Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues
   Application.CutCopyMode = False
   Next i

Thanks

Rup
0
ccfcrup
12/11/2009 8:01:12 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
659 Views

Similar Articles

[PageSpeed] 46

Check your other post.

ccfcrup wrote:
> 
> I have the procedure below. Basically I don't want to have
> interminable lines of code when all I am doing is repeating the same
> procedure on a differnet range. How can I write the procedure that it
> loops through the procedure with the 6 different ranges
> 
>    rng1 = "A1:A9"
>    rng2 = "A11:A20"
>    Rng3 = "A30:A34"
>    Rng4 = "A40:A48"
>    Rng5 = "A50:A59"
>    Rng6 = "A65:A69"
> 
>    n = 6
> 
>    For i = 1 To n
> 
>    ActiveCell.Range(rng(i)).Select
>    Selection.Copy
>    Selection.PasteSpecial Paste:=xlPasteValues
>    Application.CutCopyMode = False
>    Next i
> 
> Thanks
> 
> Rup

-- 

Dave Peterson
0
Dave
12/11/2009 8:25:14 PM
set your ranges in an array

Dim rng(6) As String
rng(1) = "A1:A9"
rng(2) = "A11:A20"
rng(3) = "A30:A34"
rng(4) = "A40:A48"
rng(5) = "A50:A59"
rng(6) = "A65:A69"

n = 6

For i = 1 To n
   ActiveCell.Range(rng(i)).Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues
   Application.CutCopyMode = False
Next i

Using the ActiveCell.Range method give you a relative reference.  If you 
want the ranges to always be A1:A9, .. etc, remove the ActiveCell. and just 
use Range(rng(i)).select
-- 
If this helps, please remember to click yes.


"ccfcrup" wrote:

> I have the procedure below. Basically I don't want to have
> interminable lines of code when all I am doing is repeating the same
> procedure on a differnet range. How can I write the procedure that it
> loops through the procedure with the 6 different ranges
> 
>    rng1 = "A1:A9"
>    rng2 = "A11:A20"
>    Rng3 = "A30:A34"
>    Rng4 = "A40:A48"
>    Rng5 = "A50:A59"
>    Rng6 = "A65:A69"
> 
> 
>    n = 6
> 
>    For i = 1 To n
> 
>    ActiveCell.Range(rng(i)).Select
>    Selection.Copy
>    Selection.PasteSpecial Paste:=xlPasteValues
>    Application.CutCopyMode = False
>    Next i
> 
> Thanks
> 
> Rup
> .
> 
0
Utf
12/11/2009 9:08:01 PM
Reply:

Similar Artilces: