CODEWITHSUNDEEP
vbaexcel
user4242750
user4242750

Reputation: 197

Row Number reference with VBA

I have searched a bit for a VBA code that will list me a row reference and am not finding results. Perhaps I am missing what the actual term for it is?

I have a list of names in Column A, starting at A2. Then what I would like is a listing of 1,2,3,4,5 going down Column B, starting from B2, until the names stop.

I can do this as a formula but need to have the values set there by a macro in this case.

Can this be done?

Upvotes: 2

Views: 9129

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

No need for a loop:

Sub NumberRows()

With Sheets("Sheet Name Here")
    With .Range("B2:B" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        .Formula = "=ROW()-1"
        .Value = .Value
    End With
End With

End Sub

Upvotes: 2

quantum285
quantum285

Reputation: 1032

If I understand you correctly then this should work:

Sub test()

    Dim lastRow As Long, counter As Long
    Dim cell As Range
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("NAME_OF_YOUR_WORKSHEET")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    counter = 1
    For Each cell In ws.Range("B2:B" & lastRow)
        cell.Value = counter
        counter = counter + 1
    Next cell

End Sub

Upvotes: 2

Related Questions