In VBA (Excel) how can Column A insert an ID header followed by 1-X UNTIL it reaches the empty cell representing the highest row with data?

Question

I am trying to create a macro that will insert a column, which is to be the first column in the spreadsheet (A) while shifting all original columns over 1 column to the right.

I then need this first column to create the header "ID" with each one numerically counting the rows:

[A]

ID

1

2

3

I only want the numbering to stop once it has reached the last relevant row in the spreadsheet. I was able to generate the following VBA by doing what I would normally do to accomplish this task while recording the macro and ended up with this:

Sub InsertID()
'
' InsertID Macro
' Add first column to be 1-##
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A2:A3").Select
    Selection.AutoFill Destination:=Range("A2:A522")
    Range("A2:A522").Select
    Range("A1").Select
End Sub

Obvoiusly, this doesn't work for my situation. The template I was using only had 521 rows. This number is going to be a variable which can usually be determined by the number of rows in the original column A (Which is now column be after running this macro).

I have extensively looked into how to create a variable for number of rows in a specific column but have been unable to find a question that has similar enough parameters even though it seems so simple.

Thanks in advance

See this for ideas on how to find the *last* row in a given range: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba — David Zemens, Apr 10 '17 at 15:13
See also this for ideas & reasons to avoid using `Select` method: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros :) — David Zemens, Apr 10 '17 at 15:14
Also, you don't need to use `FormulaR1C1` if you're just setting a value there. Just use `.Value` instead. — BruceWayne, Apr 10 '17 at 15:32

score 4 · Answer 1 · answered Apr 10 '17 at 15:35

4

Try this...

Sub CreateIDColumn()
lr = ActiveSheet.UsedRange.Rows.Count
Columns(1).Insert
Range("A1").Value = "ID"
Range("A2:A" & lr).Formula = "=ROW()-1"
Range("A2:A" & lr).Value = Range("A2:A" & lr).Value
End Sub

answered Apr 10 '17 at 15:35

Subodh Tiwari sktneer

9,906
2
18
22

score 0 · Answer 2 · answered Apr 10 '17 at 15:38

I believe the following code will do what you want to do. It declares variables (in case Option Explicit is declared - which it should be), inserts the column, finds the last row (if there is one), and inserts relevant data.

Private Sub InsertID()
    Dim lastrow, i As Integer 'declaring variables

    'adding column
    Range("A1").EntireColumn.Insert

    'getting last row #
    If Application.WorksheetFunction.CountA(Cells) <> 0 Then
        lastrow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    Lookat:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    Else
        lastrow = 1
    End If

    'setting value of cell A1
    Cells(1, 1) = "ID"

    'setting value for the rest of the cells in column A
    For i = 2 To lastrow
        Cells(i, 1) = i - 1
    Next

End Sub

In VBA (Excel) how can Column A insert an ID header followed by 1-X UNTIL it reaches the empty cell representing the highest row with data?

2 Answers2