CODEWITHSUNDEEP
excelvba
krnactry
krnactry

Reputation: 57

Fill column with numbers with 4 visible digits based on row

In the column AS, I have a blank column where I need to give a number to each row starting from row 3 to the last row (this is already predefined as lastrow(row number) in the VBA). Is there any way to number them from 1 to the last row in VBA programming?

For example, in the Column AS i would like to have

Row 3: 1
Row 4: 2
Row 5: 3
....
Last Row: XXX

If possible, how do you format the numbers so that they are all 4 digits

0001,
0002,
0003,
etc

Upvotes: 0

Views: 822

Answers (4)

Dan Donoghue
Dan Donoghue

Reputation: 6216

Here is a solution to give you the numbers as text and without looping:

Range("AS3:AS" & Range("B" & Rows.Count).End(xlUp).Row).formula = "=Right(""000"" & Row(AS3) - 2,4)"
Range("AS:AS").copy
Range("AS:AS").PasteSpecial xlPasteValues

Uses the ROW() function in excel then copies and pastes as values.

Assumes you have data in column B

Upvotes: 0

Bond
Bond

Reputation: 16321

There is a much better solution that what's been offered. Excel has a Fill Series feature that can perform trends and linear progressions. It's a simple one-line function call to DataSeries() in VBA:

Range("AS3") = 1                             ' Set the starting value

With Range("AS3:AS" & intLastRow)
    .DataSeries xlColumns, xlLinear, , 1     ' Linear series, step = 1
    .NumberFormat = "0000"                   ' Pad with zeros
End With

Upvotes: 1

user4691433
user4691433

Reputation:

MatthewD's code is fine, but my .NumberFormat method is a little cleaner, and I don't use the problematic UsedRange.

Sub M1FourDigitRow()
'
' M1FourDigitRow Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Dim Range1 As Range
Dim Cell1 As Range

Set Range1 = ThisWorkbook.Sheets("Sheet9").Range(ThisWorkbook.Sheets("Sheet9").Cells(3, "AS"), ThisWorkbook.Sheets("Sheet9").Cells(1000, "AS"))
For Each Cell1 In Range1
    Cell1.Value = Cell1.Row - 2
    Cell1.NumberFormat = "0000"
Next Cell1

End Sub

Upvotes: 2

MatthewD
MatthewD

Reputation: 6771

Dim lRow As Long
Dim lCount as Long
Dim ws as excel.worksheet
Set ws = Application.ActiveSheet
Dim strCount As String

    lRow = 3
    lCount  = 1
    Do While lRow <= ws.UsedRange.Rows.count

    strCount = Trim(str(lCount))
    If Len(strCount) = 1 Then
        ws.Range("AS" & lRow).Value = "'000" & strCount
    ElseIf Len(strCount) = 2 Then
        ws.Range("AS" & lRow).Value = "'00" & strCount
    ElseIf Len(strCount) = 3 Then
        ws.Range("AS" & lRow).Value = "'0" & strCount
    Else
         ws.Range("AS" & lRow).Value = "'" & strCount
    End If

    lCount = lCount + 1
    lRow = lRow + 1
    Loop

Upvotes: 1

Related Questions