# Thread: How to create a range from a cell value and repeat time in another cell ? Thanks: 0 Likes: 0

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

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 ?

2. ## Re: How to create a range from a cell value and repeat time in another cell ?

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]```

3. ## Re: How to create a range from a cell value and repeat time in another cell ?

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.

4. ## Re: How to create a range from a cell value and repeat time in another cell ?

I don't understand what you want to do.

5. ## Re: How to create a range from a cell value and repeat time in another cell ?

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 ?

6. ## Re: How to create a range from a cell value and repeat time in another cell ?

Perhaps :
Enter in A1:A100 =\$B\$1

7. ## Re: How to create a range from a cell value and repeat time in another cell ?

Originally Posted by mrchonginhk
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.