Some basic EXCEL VBA coding Questions

Question

2 Requests Here for MS EXCEL 2010 & 2013

1. Here the Range is hard Coded as B9:W9, what I want is to have this range value to be gathered from a Cell, say B4

(B4 will contain the text B9:W9 and be gathered from formula)

Code:

Range("B9:W9").Resize(Range("B2").Value).FillDown

2. Once the values are coded to be gathered from a cell, is there a way to have these values move if I ad or remove Columns or Rows?

ie for the section above .....Resize(Range("B2").Value)....... If I add a Column before B, the value the Macro is looking for remains as B2, but this change has moved the value in the Sheet to C2.

Is there a way to have the code follow the CELL as it is moved, like Cell formula follow the cells?

Thanks in advance,

Regards

mbart67

Answer 1

You can use:

Range(Range("B4").Value).Resize(Range("B2").Value).FillDown

for the first part. For the second, you could assign a name to the cell, then use that name in the code. Eg select B2 and then type ResizeValue in the name box on the left of the formula bar and press enter. Then in the code refer to Range("ResizeValue").Value

Answer 2

Thank You :)