How do I add a column with 0, 1, 2, and 3 in Excel using VBA?

To add a column with the values 0, 1, 2, and 3 in Excel using VBA, you can use a simple loop and the Cells property to write the values into the desired column. Here's how you can do it:

1. Open the VBA Editor:

- Press `Alt + F11` to open the Visual Basic for Applications (VBA) editor.

2. Insert a Module:

- Go to `Insert` > `Module` to create a new module where you'll write your VBA code.

3. Write the VBA Code:

- Here is the VBA code that adds a column and fills it with values 0, 1, 2, and 3.

Sub AddColumnWithSequence()
  Dim i As Integer
  Dim lastRow As Long

  lastRow = Cells(Rows.Count, "A").End(xlUp).Row
  If lastRow < 4 Then
    lastRow = 4
  End If
     For i = 0 To 3
    Cells(i + 1, "B").Value = i
  Next i

     Range("B1:B" & lastRow).EntireColumn.AutoFit
End Sub

4. Explanation of the Code:

- `Sub AddColumnWithSequence()`: This line starts the VBA subprocedure.

- `Dim i As Integer`: Declares an integer variable `i` to be used as a counter in the loop.

- `Dim lastRow As Long`: Declares a variable to find the last row.

- `lastRow = Cells(Rows.Count, "A").End(xlUp).Row`: This finds the last row from column "A".

- `If lastRow < 4 Then lastRow = 4`: This checks if last row found is less then 4, then it uses 4.

- `For i = 0 To 3`: This loop iterates from 0 to 3.

- `Cells(i + 1, "B").Value = i`: This writes the value of `i` into column "B" at the respective row. The `i + 1` is necessary as Excel rows start from 1 and not 0.

- `Range("B1:B" & lastRow).EntireColumn.AutoFit`: This auto fits the column B.

- `Next i`: Moves to the next iteration of the loop.

- `End Sub`: Ends the VBA subprocedure.

5. Run the Macro:

- Go back to your Excel sheet.

- Press `Alt + F8` to open the Macro dialog.

- Select `AddColumnWithSequence` and click `Run`.

- A new column will be added with the sequence 0, 1, 2, 3 in column "B". If there was more data in column "A", it autofits to that.

This VBA code will create a new column (column B) and fill it with the values 0, 1, 2, and 3 in the first four rows. You can adjust the column letter and the number of rows based on your requirements.

Learn more about this