Excel Macro for a Dynamic Range of Cells

Question

I would like to get the contents of a cell (a number, dynamically generated) and use this to define a cell range for my macro in Excel 2007.

Let's say my macro is:

ActiveCell.FormulaR1C1 = "=RC[1]*6"
Range("A1").Select
Selection.Copy
Range("A2:A12").Select
ActiveSheet.Paste

Let's say cell C5 contains the number 8. Instead of using the range A2:A12, I'd like to be able to get the number 8 from cell C5, add 2 to it and have the range A2:A10.

If C5 = 8, my range will be A2:A10

If C5 = 9, my range will be A2:A11

If C5 = 10, my range will be A2:A12

and so on.

How would I change the vba code to achieve this?

Thankyou for any help!

Michael Michael · Accepted Answer · 2013-12-05T12:04:54

Try this:

Range("A2:A" & Range("C5").Value + 2).Select

Another method is using Resize, like Sam suggested:

Range("A2").Resize(Range("C5").Value + 2)

On another note, interesting link: Avoid using Select

Excel Macro for a Dynamic Range of Cells

1 Answers