Fill series VBA code

[mira2020]

Hi all,

i want to write VBA code to fill column A with a series number start with 1, it will be fill until the last value in column B.
Here is my code:

ActiveCell.FormulaR1C1 = "1"
Range("A2").AutoFill Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row), xlFillSeries

it works fine except when the column B has only 1 value , that mean column A should have only "1", there is an error "AutoFil method of RAnge class failed "

Any suggestion? thanks

 

[BigBeachBananas]

How about this?

Sub fillNum()
    Dim lastRow As Long
    
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
   
    Range("A1").Value = 1
    If lastRow > 1 Then
        Range("A1:A" & lastRow).FillDown
    End If
End Sub

 

[rlv01]

Another variant

    Dim rng As Range
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    
    Range("A2") = 1
    If rng.Rows.Count > 1 Then
        Range("A2").AutoFill rng.Offset(0, -1), xlFillSeries
    End If

 

[mira2020]

thank you @rlv01

i have 2 fill series running on 2 sheets as below, in the same sub:

Dim rng As Range
Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))

Range("A2") = 1
If rng.Rows.Count > 1 Then
Range("A2").AutoFill rng.Offset(0, -1), xlFillSeries
End If


Dim rng1 As Range
Set rng1 = Range("Q2", Range("Q" & Rows.Count).End(xlUp))

Range("V2") = 1
If rng1.Rows.Count > 1 Then
Range("V2").AutoFill rng1.Offset(0, -1), xlFillSeries
End If

the first one work well, the second one has below error .
not sure what is the problem ?

 

[Rick Rothstein]

Here is an different approach...

Sub FillColA()
  With Range("B2", Cells(Rows.Count, "B").End(xlUp))
    .Offset(, -1) = Evaluate("Row(1:" & .Rows.Count & ")")
  End With
End Sub