Cell referencing in VB Editor

  • Thread starter Thread starter platinum_eye200
  • Start date Start date
P

platinum_eye200

Hi,

I want to reference a range of cells in the Visual basic editor. The
syntax i can use is:

Selection.AutoFill Destination:=Range("A10:H12"), Type:=xlFillDefault


except i want the number of columns referenced to be equal to the
variable 'n' which changes during runtime.

i.e.

Selection.AutoFill Destination:=Range("A10:Hn"), Type:=xlFillDefault

but this is rejected.

I would be grateful for any suggestions anyone has.

Martin.
 
Hmmm.........the original question says the number on COLUMNS wil
change.

But since H is the column then "H" & n wont work.

Maybe the questioner meant to say the number of rows will change. The
your suggestion will wor
 
Hi Platinum_eye200,
I want to reference a range of cells in the Visual basic editor. The
syntax i can use is:

Selection.AutoFill Destination:=Range("A10:H12"), Type:=xlFillDefault

except i want the number of columns referenced to be equal to the
variable 'n' which changes during runtime.

i.e.

Selection.AutoFill Destination:=Range("A10:Hn"), Type:=xlFillDefault

but this is rejected.

I would be grateful for any suggestions anyone has.
Click to expand...

Instead of specifying an actual range, you can use a combination of the
Cells and Resize methods to identify the range:

... Destination:=Cells(Top,Left).Resize(Height,Width),...

Where Top, Left, Height and Width are all numbers, so A10:H12 is:

Cells(10,1).Resize(3,8)

Or if A10 is always fixed, you can use that instead of Cells(10,1):

Range("A10").Resize(3,8)

Of if you know the actual row number you want to stop at (instead of the
height of the range), you could use the Range() method where you provide
the top-left and bottom-right corners using either the Range("Address")
or Cells(x,y) alternatives (or indeed any other way of identifying a
specific cell):

Range(Range("A10"), Cells(12,8))

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Back
Top