CODEWITHSUNDEEP
excel
user3234810
user3234810

Reputation: 482

Generating number list for mail merge

Trying to print a lot of labels, I need column A in excel to read something like.

EH001`
EH001
EH001
EH001
EH002
EH002
EH002
EH002
EH003.......

I will need to do this for thousands of samples, Whats the easiest way to generate this list?

Upvotes: 0

Views: 31

Answers (1)

Goos van den Bekerom
Goos van den Bekerom

Reputation: 1493

Try something like this:

Dim NextRow As Long
NextRow = Thisworkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlup).Row + 1

For i = 1 To 10000 'or however many you need
    If i < 10 Then
        Range("A" & NextRow).Value = "EH00" & i
    ElseIf i >= 10 And i < 100 Then
        Range("A" & NextRow).Value = "EH0" & i
    ElseIf i >= 100 And i < 1000 Then 
        Range("A" & NextRow).Value = "EH" & i
    End If ' You can go on like this for as much as you need.
Next i

Upvotes: 1

Related Questions