Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Fill cells down column x number of time with repeating numbers

1. I agreed to these rules
Join Date
29th December 2011
Posts
4

## Fill cells down column x number of time with repeating numbers

I need help finding a way to fill and then repeat cell values in a collum in a series. I need to make an entry then fill the next 26 cells with the same value then go to the next value in the series and repeat the new value 26 more times and keep repeating this 2000 times. Copy and paste will take days. Below is a small version of what i need completed

1
1
1
1
2
2
2
2
3
3
3
3
4
4
4
4
all the way up to about 2225
Last edited by AAE; December 29th, 2011 at 10:35. Reason: revise thread title

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Repeating cells in a series?

If you mean from the column above take the first value and copy it to a column 26 times then take the next value and copy it 26 times all the way down the column - Assuming the values you listed are in column A
VB:
```Option Explicit

Sub repColEntry()
Application.ScreenUpdating = False
Dim t As Long, x As Long
x = 1
For t = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(t, 1).Copy
Range("B" & x & ":B" & x + 25).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
x = x + 26
Next
Application.ScreenUpdating = True
End Sub

```

3. ## Re: Repeating cells in a series?

A non looping approach
VB:
```Dim maxNumber As Long, numberOfRepeats As Long

numberOfRepeats = 26
maxNumber = 2225

With Range("A1").Resize(numberOfRepeats * maxNumber, 1)
.FormulaR1C1 = "=INT((ROW()-1)/" & CStr(numberOfRepeats) & ")"
.Value = .Value
End With

```

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
29th December 2011
Posts
4

## Re: Fill cells down column x number of time with repeating numbers

Wow, thank you both for the quick responce and problem solving! Only one more question, what do I do with the coding you have provided. sorry so inexperenced.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Fill cells down column x number of time with repeating numbers

The code provided is intended to go into a Standard Module (as opposed to a Class Module.

To add the code

1. Press Alt + F11 to open the VB Editor
2. Menu > Insert > Module (not class module)
3. Copy and paste the above code into the code pane
4. Make any necessary adjustments to sheet/range references
5. Alt + Q to close the Vb Editor

To run the macro:
Alt + F8 to invoke the Run Macro Dialog
Choose the macro and click on Run

6. I agreed to these rules
Join Date
29th December 2011
Posts
4

## Re: Fill cells down column x number of time with repeating numbers

i dont think the macro is saving. its not listed in the dialog box that ALT + F8 provides, everything opens and closes as you said it would but appears its not adding the marco to the list to choose from

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Fill cells down column x number of time with repeating numbers

I forgot to mention that with Mike's code you must give the macro a name.

So, follow the steps above, but before step-3, in the code pane type the following or something similar and press enter key

Sub Repeat_Numbers

VBA will automatically add "End Sub"

If using Mike's code copy and paste the code just after the Sub Repeat_Numbers line.

Anthony's code may copied and pasted in full without further need to manipulate VBA.

If you are using Excel 2007+ you must save the file as a macro-enabled file type (.xlsm).

8. I agreed to these rules
Join Date
29th December 2011
Posts
4

## Re: Fill cells down column x number of time with repeating numbers

i got the looping code to work perfect, thank you, only thing is that it puts the new series in the next row. but nothing a quick copy and paste cant fix. THANK YOU!!!

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Fill cells down column x number of time with repeating numbers

To save you cut and pasting you could amend the code to
VB:
```Option Explicit

Sub repColEntry()
Application.ScreenUpdating = False
Dim t As Long, x As Long
x = 1
For t = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(t, 1).Copy
Range("B" & x & ":B" & x + 25).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
x = x + 26
Next
Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Clear
End Sub

```
As a general rule if you can avoid loops do so. I would recommend you try Mike's code
VB:
```Sub copyRows()
Sub copyRows()
Dim maxNumber As Long, numberOfRepeats As Long

numberOfRepeats = 26
maxNumber = 2225

With Range("A1").Resize(numberOfRepeats * maxNumber, 1)
.FormulaR1C1 = "=INT((ROW()-1)/" & CStr(numberOfRepeats) & ")"
.Value = .Value
End With
End Sub

```
No loops, shorter code!!!

10. I agreed to these rules
Join Date
4th April 2011
Posts
13

## Re: Fill cells down column x number of time with repeating numbers

what code do i use if i want to add the numbers 1 to 100 in column A repeating each number 5 times . The above code does when i used was giving me the same value throughout . Sorry i am new to vba .

Excel Video Tutorials / Excel Dashboards Reports

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno