AutoFill series numbers in a column

[fhzhkunming]

Hi All,

I want to autofill series numbers in column F. Basically, F2 = 1, F3=2..., the length of the series depends on row numbers of Column G (Fig).

Here is my code, but I got run-time error 1004': Application-defined or object-defined error, which I can't figure out. Could someone help me out?

Sub Autofill_seriers()
    Dim Lrow As Long
    Lrow = Range("G2:G" & Rows.Count).End(xlUp).Row
    With Sheets("Lat Longs-2")
        Range("F2").Value = 1
        Range("F3").Value = 2
        Range("F2:F3").AutoFill Destination:=.Range("F2:F" & Lrow)  'run-time error '1004'
    End With
      
End Sub

Thanks

 

[Fluff]

How about

Sub Autofill_seriers()
    Dim Lrow As Long
    
    With Sheets("Lat Longs-2")
        Lrow = .Range("G" & Rows.Count).End(xlUp).Row
        .Range("F2").Value = 1
        .Range("F3").Value = 2
        .Range("F2:F3").AutoFill Destination:=.Range("F2:F" & Lrow)  'run-time error '1004'
    End With
End Sub

 

[fhzhkunming]

How about

Sub Autofill_seriers()
    Dim Lrow As Long
   
    With Sheets("Lat Longs-2")
        Lrow = .Range("G" & Rows.Count).End(xlUp).Row
        .Range("F2").Value = 1
        .Range("F3").Value = 2
        .Range("F2:F3").AutoFill Destination:=.Range("F2:F" & Lrow)  'run-time error '1004'
    End With
End Sub

It's working perfectly. Thank you very much.

Frank

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

Just as a point of interest, here is another way to write this macro...

Sub AutoFillSeriers()
  With Sheets("Lat Longs-2")
    .Range("F2:F" & .Cells(Rows.Count, "G").End(xlUp).Row) = [ROW(1:1000)]
  End With
End Sub

Note: The above code assumes you will not have more than 1000 rows of data. If you could have more, change the highlighted 1000 to a number that will always be larger than your maximum number of data rows.

 

[fhzhkunming]

Just as a point of interest, here is another way to write this macro...

Sub AutoFillSeriers()
  With Sheets("Lat Longs-2")
    .Range("F2:F" & .Cells(Rows.Count, "G").End(xlUp).Row) = [ROW(1:1000)]
  End With
End Sub

Note: The above code assumes you will not have more than 1000 rows of data. If you could have more, change the highlighted 1000 to a number that will always be larger than your maximum number of data rows.

Thanks, Rick. Good to know that there is another way to do it. But exactly to the Range("F2:F" & Lrow) is perfect to me.

Frank