Macro to automatically number rows based on a number

sean_nyc · Dec 1, 2014

Hi there,

I am new to the group and was curious if anyone can help me out with a macro I need to create. I am trying to number rows based on a number in the header. However, I want to repeat a number a certain number of times until it moves on to the next number.

To provide more detail:
Number of rows I want to have the same number for: 2
A1: 5

Expected result:
Row 2: 1
Row 3: 1
Row 4: 2
Row 5: 2
Row 6: 3
Row 7: 3
Row 8: 4
Row 9: 4
Row 10: 5
Row 11: 5

This will then need to be extrapolated to be repeated for 45 rows.

Thank you!

Snakehips · Dec 1, 2014

sean_nyc,

Welcome to MrExcel.

Not sure if I'm understanding the 'repeated for 45 rows' ????

Try something like....

Code:

Sub NumberRows()
m = 45 'last row *******??????
c = 2 'same number
n = 0  
For r = 2 To m Step c
n = n + 1
Cells(r, 1) = n
Cells(r, 1).Offset(1, 0) = n
If n = Cells(1, 1) Then n = 0
Next r
End Sub

Hope that helps.

Marcelo Branco · Dec 1, 2014

Welcome to Mr Excel

You can achieve this using a formula.

Something like this

[Table="class: grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][/tr]
[tr][td]

1

[/td][td]

5

[/td][td] [/td][td]

Repeat

[/td][/tr]

[tr][td]

2

[/td][td]

1

[/td][td] [/td][td]

2

[/td][/tr]

[tr][td]

3

[/td][td]

1