VBA in EXCEL. AUTOFILL RIGHT

Question

How can I do an autofill to the right?

I've searched and tried to replicate the same steps that I saw on other threads but it didn't worked for me. Maybe for some lack of intuition.

My sheet is generating a new sheet from VBA (its a monte carlos simulation). I want to generate a new line on top of the matrix and fill the line from B1 to the last column with "0,1,2,...,X"

I've tried the following code:

Sheets("valores").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = "0"
Range("B2").Select
Selection.End(xlToRight).Select
Range("LastColumn"&1).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step _
    :=1, Trend:=False

  
  MsgBox "Sheet generated."
  

Can someone give me some help on this? Thank you!

Answer 1

Supposing you want to fill to the right from cell B1 to cell X, incrementing the value 1 serially, then you could use the following code: Assuming cell X is for example Z1, you have:

Range("B1").Value=0
Range("B1").Select
Selection.Autofill Destination :=Range("B1:Z1"), Type 
:=xlFillSeries

If the range is dynamic then you can try the following:

Private Sub AutoFill()
Dim lColumn As Long
Dim rango As String
Dim nColumn As String
lColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Cells(2, lColumn).Select
rango = ActiveCell.Address
nColumn = Mid(rango, InStr(rango, "$") + 1, InStr(2, rango, "$") - 2)
Range("B1").Value = 0
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:" & nColumn & "1"), Type:=xlFillSeries
MsgBox ("AutoFill is completed for: " & "B1:" & nColumn & "1")
End Sub

I have supposed that your data is located at Raw 2, starting at cell B2. The previous code will use the Raw 2 in order to look for the last column.