Fill column with same number

Keala

New Member
Joined
Jul 9, 2018
Messages
37
This is probably very easy for someone who knows VBA better then me. But what I want to achieve is to populate let say five rows in same column with same number then add one to that number and populate next five rows with the new number and so on, let say do this 100 times (last number is 240). So the output I want is to look like this

140
140
140
140
140
141
141
141
141
141
142...

Thank you for your help
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try this:


Assuming you want the values into column A

Code:
Sub Resize_Me()
'Modified 9/22/2018 9:04 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim x As Long
x = 140
    For i = 1 To 501 Step 5
        Cells(i, 1).Resize(5).Value = x: x = x + 1
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Here is a general routine using a different algorithm than My Aswer Is This used which allows you to set the start number, ending number, number of repeats before incrementing the number and the cell to start the downward list in...
Code:
[table="width: 500"]
[tr]
	[td]Sub ListWithRepeats()
  Dim Start As Long, Finish As Long, Repeat As Long, StartCell As String
  Start = [B][COLOR="#0000FF"]140[/COLOR][/B]
  Finish = [B][COLOR="#0000FF"]240[/COLOR][/B]
  Repeat = [B][COLOR="#0000FF"]5[/COLOR][/B]
  StartCell = "[B][COLOR="#0000FF"]A1[/COLOR][/B]"
  Range(StartCell).Resize(Repeat * (Finish - Start + 1)) = Application.Transpose(Split(Join(Evaluate("TRANSPOSE(IF({1},REPT(ROW(" & Start & ":" & Finish & ")&"" ""," & Repeat & ")))"), "")))
End Sub[/td]
[/tr]
[/table]
Note: There is one restriction on the method I used above... the number of outputted values cannot exceed a total of 65535 individual values (no matter what your start value, finish value or repeat count is).
 
Last edited:
Upvote 0
Here's another way.
Select the start cell and then run.
Code:
Sub ListWithRepeats()
Dim Start As Long, Finish As Long, Repeat As Long
Start = [B][COLOR=#0000cd]140[/COLOR][/B]
Finish = [COLOR=#0000cd][B]240[/B][/COLOR]
Repeat = [COLOR=#0000cd][B]5[/B][/COLOR]
With Selection.Resize((Finish - Start+1) * Repeat)
    .Formula = "=INT((ROW()-ROW(" & Selection.Address(1, 0) & "))/" & Repeat & ")+140"
    .Value = .Value
End With
End Sub

let say do this 100 times (last number is 240)
That would be 101 times.
 
Last edited:
Upvote 0
Here's another way.
Select the start cell and then run.
Code:
Sub ListWithRepeats()
Dim Start As Long, Finish As Long, Repeat As Long
Start = [B][COLOR=#0000cd]140[/COLOR][/B]
Finish = [COLOR=#0000cd][B]240[/B][/COLOR]
Repeat = [COLOR=#0000cd][B]5[/B][/COLOR]
With Selection.Resize((Finish - Start+1) * Repeat)
    .Formula = "=INT((ROW()-ROW(" & Selection.Address(1, 0) & "))/" & Repeat & ")+140"
    .Value = .Value
End With
End Sub
That would be 101 times.
Why are you having Excel go through the trouble of placing multiple formulas in the cells, create the precedents/dependents linkages (not 100% sure what that all entails under the hood, but it needs to establish these in some fashion for each cell reference in the formula) and whatever else goes along with placing a formula in a cell only to convert the formula to values and have it unhook all the precedents/dependents it just created (as well as undo any other attendant processes that go along with placing a formula in a cell) when the final value can be placed in the cell directly using code similar to the code I posted in Message #3 .

As an aside, your code included a +140 as the end of the formula text string... I am pretty sure that 140 should be changed to a concatenation of the Start variable instead.
 
Upvote 0
Why are you having Excel go through the trouble of placing multiple formulas in the cells, create the precedents/dependents linkages (not 100% sure what that all entails under the hood, but it needs to establish these in some fashion for each cell reference in the formula) and whatever else goes along with placing a formula in a cell only to convert the formula to values and have it unhook all the precedents/dependents it just created (as well as undo any other attendant processes that go along with placing a formula in a cell) when the final value can be placed in the cell directly using code similar to the code I posted in Message #3 .
No big deal. Are you having a bad day?

As an aside, your code included a +140 as the end of the formula text string... I am pretty sure that 140 should be changed to a concatenation of the Start variable instead.
Yes.
 
Upvote 0
No big deal.
To expand : if the "Finish" is increased from 240 to 13246 (which is the maximum your macro can handle), my macro takes less than 1/4 of a second.
Your macro should be quicker - maybe saves about 1/10 of a second?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top