0

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!

pnuts
  • 58,317
  • 11
  • 87
  • 139
whispersan
  • 1,029
  • 2
  • 13
  • 28
  • [Resize](http://www.homeandlearn.org/the_resize_property.html) and [Offset](http://www.ptraining.com/blog/2010/10/using-range-offset-in-excel-vba/) Would be a good place to start looking. – Sam Dec 05 '13 at 12:01

1 Answers1

1

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

Community
  • 1
  • 1
Michael
  • 538
  • 5
  • 8
  • Thankyou that really helped. Just the small things sometimes - my first venture in to vba and macros and it made my project possible, without needing to spend tens or hundreds of hours researching or learning the language to get the job done. – whispersan Dec 05 '13 at 23:43