1. ## Automatically Number Column

Hi,

On my worksheet in Column D a start number is given and size/end is given in Col E. I want the results to start from Column H.
Example: (1) Col D 350 Col E 50 (2) Col D 101001 Col E 10

(1)So from Col H it will write the number 000350 next col I 000351 next J 000352 till it reaches Col BE 000400.
(2)So from Col H it will write the number 101001 next col I 101002 next J 101003 till it reaches Q 101010

It has to be 6 digits if the start number having 3 digits it will add 3 zeros before that and complete it with total 6 digits.

2. foncesa,

This code will increment the numbers in the rows starting at column H using the parameters in Col D and E. It will format each of the cells with leading zeros to have a 6 decimal number.

HTH
Maud

Code:
```Public Sub FormatCells()
Dim I As Integer, J As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For I = 1 To LastRow
Cells(I, 8) = Cells(I, 4)
Cells(I, 8).NumberFormat = "000000"
For J = 9 To Cells(I, 5) + 8
Cells(I, J) = Cells(I, J - 1) + 1
Cells(I, J).NumberFormat = "000000"
Next J
Next I
End Sub```

3. Thanks Maudibe,

Its incrementing the numbers in Col H, but its incrementing one 1 additional.
I mean if the Col D starting states 1 & Col E states end 5 it starts incrementing in Col H 1, Col I 2, Col J 3, Col K 4, Col L 5, Col M 6

4. Simple change of the 8 to a 7 in line 7

HTH
Maud

Code:
```Public Sub FormatCells()
Dim I As Integer, J As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For I = 1 To LastRow
Cells(I, 8) = Cells(I, 4)
Cells(I, 8).NumberFormat = "000000"
For J = 9 To Cells(I, 5) + 7
Cells(I, J) = Cells(I, J - 1) + 1
Cells(I, J).NumberFormat = "000000"
Next J
Next I
End Sub```

5. Thankyou Maud,

I tested its working, but on 50000 rows it throws an Run-time error '6' Overflow.

6. foncesa,

Change the line:

Dim I As Integer, J As Integer

to

Dim I As Long, J As Long

HTH,
Maud

7. Aha!
Maud, you fell into the trap.

Another way is to delete the line..
Dim I As Integer, J As Integer
..completely!
And then it will work. And the code is shorter.

RG: I rest my case again. Sometimes when you Dim variables it can cause a routine to fail (if you get the Dim wrong). If you leave the Dim out, the routine works anyway.

zeddy

8. I would have never guessed there would be 50k+ rows

9. Me neither.
I can't even figure out what it's used for either.

zeddy

10. Hi,
Its typing error 5,000 rows.
Is it possible to add auto headers from column H onwards as S1,S2,S3....
Auto save this sheet as d:\office\computed.txt [Text file]

Thanks

11. foncesa,

See if these changes are what you are looking for. The header will adjust to the columns used.

HTH,
Maud

12. Hi.

Fantastic Maudibe, Thanks, along with headers please Auto save this sheet at d:\office\computed.txt [tab] in text format and exit excel.

Highly thankfull.

13. foncesa,

See if this is what you want to accomplish

14. ## The Following User Says Thank You to Maudibe For This Useful Post:

foncesa (2014-10-26)

#### Posting Permissions

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