Excel - Using row count as the boundary for a range using VBA

Excel - Using row count as the boundary for a range using VBA

Post by Richard Buttr » Fri, 05 Jan 2001 06:21:00



I have a cell which keeps track of the number of rows on a worksheet.
(The number of rows varies).

I want to be able to use this variable number when creating a range
name, and populating this range by pasting into the 500 cells in one
column, a formula from a master cell.

i.e. suppose the number of rows on the worksheet is 500, from
A10:Z509, a master formula in cell F1, and the row count cell called
Myrows which evaluates as 500.

I need some VBA code to create a range name which covers say F10:F509
(the F10 will always be the first cell),  and then copy the formula in
F1 into the range F10:F509

I'd be grateful for any help. I'm still fairly new to VBA and still
feeling my way.

TIA

 
 
 

Excel - Using row count as the boundary for a range using VBA

Post by Ivar » Fri, 05 Jan 2001 08:05:50


I'm not sure why you need to Name the output range, and you didn't indicate
what name you want to use, so I leave it to you to modify this code in
accordance with your actual needs:

Sub CopyFormulas()
  With [F10].Resize(rowsize:=[Myrows])
    .Name = "Ferdinand"    ' Put actual range name here
    [F1].Copy .Offset(0, 0)
  End With
End Sub

Paul


Quote:>I want to be able to use this variable number when creating a range
>name, and populating this range by pasting into the 500 cells in one
>column, a formula from a master cell.

>i.e. suppose the number of rows on the worksheet is 500, from
>A10:Z509, a master formula in cell F1, and the row count cell called
>Myrows which evaluates as 500.

>I need some VBA code to create a range name which covers say F10:F509
>(the F10 will always be the first cell),  and then copy the formula in
>F1 into the range F10:F509