Fill column with same number

[Keala]

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

 

[My Aswer Is This]

Try this:


Assuming you want the values into column A

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

 

[Rick Rothstein]

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...

[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).

 

[footoo]

Here's another way.
Select the start cell and then run.

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.

 

[Rick Rothstein]

Here's another way.
Select the start cell and then run.

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.

 

[footoo]

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.

 

[footoo]

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?

 

[Keala]

Thank you so much for the suggestions. That helps a lot.