# How to create a range from a cell value and repeat time in another cell ?

#### mrchonginhk

##### Well-known Member
Right now I have a data A1:A100 each cell is 500
So my range called RangeA is A1:A100
Can I do without need to have this list in this 100 rows

Now I key in, B1=500, C1=100
How can I create RangeA based on these two cell values ?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### footoo

##### Well-known Member
If you mean you want to copy the value in B1 and enter it from A1 down to the row number in C1, and then create a named range ("RangeA") which refers to the rows with data in column A :
Code:
``````Dim rng\$: rng = "RangeA"
On Error Resume Next
ActiveWorkbook.Names(rng).Delete
On Error GoTo 0
Range("A2:A" & Cells(Rows.Count, "A").End(3).Row).ClearContents
Range(rng).Value = [B1]``````

#### mrchonginhk

##### Well-known Member
Thanks. I forget I cannot use VBA in the company.

How to do it without VBA ? Will array work ?
Those 500 are fixed and do not change.

Last edited:

#### footoo

##### Well-known Member
I don't understand what you want to do.

#### mrchonginhk

##### Well-known Member
Basically I mean I need to create a range of 100 cells each is the same "500"
How to do this using cell value 500 and 100 ?

#### footoo

##### Well-known Member
Perhaps :
Enter in A1:A100 =\$B\$1

#### Peter_SSs

##### MrExcel MVP, Moderator
Now I key in, B1=500, C1=100
How can I create RangeA based on these two cell values ?
Try in A1, Copied down as far as you might ever need

=IF(ROWS(A\$1:A1)>C\$1,"",B\$1)

Now try changing B1 and/or C1 and look at the new values in column A.