Using variables with ROWs

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Is it possible to use varibles in a function such as "ROWS
(2:5).Select" (see line 70 & 80 of code below) to allow
for looping such as:

For example if SAMPLE_NUMBER=4 I want to insert the copied
cells in 3 different places.

10 For x = 1 To (Sample_Number - 1)
20 'Copy the original 4 rows
30 Rows("2:5").Select
40 Application.CutCopyMode = False
50 Selection.Copy
60 'Insert copied cells
70 y = 2 + (x * 4) 'move down four rows
80 Rows(y:y).Select 'THIS DOES NOT WORK
90 Selection.Insert Shift:=xlDown
100 'repeat this copy/paste moving 4 additional rows
each time.
110 Next x
 
Michael,

Just use the row number in the Rows object. E.g.,

Rows(y).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Michael,

When you copy a range and want to paste it (or insert it) you need only
designate a single cell for the paste or insert. Note that selection is not
required.

Selection.Copy
Cells(y,1).Insert

Rows("2:5").Copy
Range("A" & y).Insert Shift:=xlDown
 
Back
Top