Add Consecutive Numbers Excel VBA Macro

In this post we see how to add consecutive numbers to cells in the Excel worksheet using VBA macros. We review two simple options: 1. Using an classic programming For loop, and 2. Using the AutoFill function (a method of the Range object). Using AutoFill requires to add the first 2 numbers to set the sequence of the following numbers (option 2a), or adding just one number and choosing to auto fill with a series (option 2b).

Macro code:

 
  Sub AddConsecutiveNumbers()
 
      Dim LastNumber As Integer, n As Integer
 
      LastNumber = 12  'change as needed
 
      'option 1 (For loop)
      For n = 1 To LastNumber
          Range("A" & n).Value = n
      Next n
 
      'option 2a (AutoFill)
      Range("B1").Value = 1
      Range("B2").Value = 2
      Range("B1:B2").AutoFill Range("B1:B" & LastNumber)
 
      'option 2b (AutoFill with xlFillSeries)
      Range("C1").Value = 1
      Range("C1").AutoFill Range("C1:C" & LastNumber), xlFillSeries
 
  End Sub
 

Macro explained:

• First, we declare a variable (LastNumber) to set the last number in the series. Another numerical variable (n) is used to loop through numbers and rows in the first option.
• Option 1 uses a simple For loop from 1 to the last number in the series to add each consecutive number to cells in column A. Note that it uses the loop variable (n) as both the row index and the consecutive number. That needs to be updated if we choose to start the series in some other row. For example, if we choose start from row 2, we would use: Range("A" & n + 1).Value = n
• Option 2a adds the first two numbers of the series to the first two cells and then applies AutoFill to continue the series (based on those 2 digits) for as long as needed (up to LastNumber). AutoFill is a method of the Range object and accepts the destination range where the series is filled automatically based on the initial range. The initial range in this case is B1:B2. The values in that range (two cells in this case with number 1 and 2) set the basis for the sequence. AutoFill accepts a second parameter that specifies the AutoFill type, which is used in option 2b.
• Option 2b is a variation of option 2 where the AutoFill type parameter is set to xlFillSeries, thus setting the series for consecutive numbers automatically based on just the first digit. This saves one line of code compared to the previous option. AutoFill type also allows to fill date-related series such as days, weekdays, months, and years, while also allowing to auto fill cells based on formats, linear and growth trends, etc. 

This is how we Add Consecutive Numbers in Excel using VBA macros.

Other examples:

• How to Insert All Pictures In Folder with Excel VBA macros
• How to Clear Cells Based On Value with Excel VBA macros
• How to Add Conditional Formatting To Unique Values with Excel VBA macros