Fill Down But Increment in Group

[frank933]

I have items in a column like below:


50
50
50
50

What I would like to be able to do is highlight the four 50's and fill down, but I would like for it to increment and paste the next four filled like this:

51
51
51
51

Then below that the next four filled like this:

52
52
52
52

If its a macro that'll be required I'd like to to be able to change the number of rows to be repeated (4 in this case, but could change).

Any help would be appreciated.
Thanks

 

[FormR]

One simplisctic way; enter the first 4 numbers manually and then on the 5th row use a formula to add a 1 to the first number and copy down.

For example:

Excel Workbook
	A
1	50
2	50
3	50
4	50
5	51
6	51
7	51
8	51
9	52
10	52
11	52
12	52
13	53
14	53
15	53
16	53
17	54
18	54
19	54
20	54
21	55
22	55
23	55
24	55
25	56
26	56
27	56
28	56
Sheet1

 

[frank933]

Thank you. Works a treat.

 

[Rick Rothstein]

Here is a macro that does what you want (actually, it does a little bit more than what you asked for)...

Sub CopyAndIncrement()
  Dim X As Long, CellCount As Long, FullRange As Range
  CellCount = Selection.Rows.Count
  On Error GoTo NothingSelected
  Set FullRange = Application.InputBox("Select the new cells to fill (include the orignal cells in your selection)...", Type:=8)
  For X = CellCount To FullRange.Rows.Count - 1
    FullRange(1).Offset(X) = FullRange(1).Offset(X - CellCount) + 1
  Next
NothingSelected:
End Sub

To use the macro, first select the cells you want to copy down and increment, then run the macro... it will ask you to select the range to fill... make sure you select the cells you originally had selected plus the new cells you want to fill with incremented values... when you click the OK button, the cells will be filled with the incremented values.

So, at the beginning of this message, I said the macro does more than what you asked for, here is what I meant. Your request was for values that were all the same... and the code works for them, but the values do not all have to be the same. For example, if your original cells contained these values...

1
22
17
8

and you select a total of 12 cells (including the original 4) in response the the InputBox request, then the macro will fill them like this...

1
22
17
8
2
23
18
9
3
24
19
10

Each individual cell's value is increment during the repeat cycle. Of course, if all the original cell values are the same, then the repeat cycles will look like they incremented as a group (which is what you asked for), but actually they are each incrementing individually (they just happen to contain the same starting values, which is what give the illusion of being grouped).

 

[Snakehips]

frank933,

A non-vba possibility?

Your starter number in cell A1 and the number of repeats in any spare cell eg here D1

Then with formula in A2, drag down as needed. Repeats will change if you change value in D1


Excel 2007

	A	B	C	D
Repeats

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet2​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]A2[/TH]
[TD="align: left"]=$A$1+ROUNDUP(ROW()/$D$1,0)-1[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]



Hope that helps.