CODEWITHSUNDEEP
excelvbamacros
Paolo Medina
Paolo Medina

Reputation: 301

Excel macro for series autofill

I would like to ask how to create macro to autofill serial/series(1, 2, 3 and so on) from row A2 up to the end of data where there is data from Column B?

I use this code:

  Sub FillHandle()
  If IsEmpty(ActiveCell.Value) Then Exit Sub
  If ActiveCell.Row = Rows.Count Then Exit Sub
  If ActiveCell.Column > 1 Then
    If Not IsEmpty(ActiveCell.Offset(, -1)) And Not IsEmpty(ActiveCell.Offset(1, -1)) Then
      Range(ActiveCell, ActiveCell.Offset(, -1).End(xlDown).Offset(, 1)).FillDown
      Exit Sub
    End If
  End If
  If ActiveCell.Column < Columns.Count Then
    If Not IsEmpty(ActiveCell.Offset(, 1)) And Not IsEmpty(ActiveCell.Offset(1, 1)) Then
      Range(ActiveCell, ActiveCell.Offset(, 1).End(xlDown).Offset(, -1)).FillDown
      Exit Sub
    End If
  End If
End Sub

However, it just autofill "1" all through out.

Below is the format of my excel sheet.

enter image description here

Thank you in advance!

Upvotes: 0

Views: 1249

Answers (2)

user4039065
user4039065

Reputation:

The Range.AutoFill method is not the same as the Range.DataSeries.

Sub FillHandle()
    Dim lr As Long

    'do not know what this is for
    If IsEmpty(ActiveCell.Value) Then Exit Sub
    'do not know what this is for either
    If ActiveCell.Row = Rows.Count Then Exit Sub

    With Worksheets("Sheet2")   '<~~ you should always know what worksheet you are on
        lr = .Cells(Rows.Count, "B").End(xlUp).Row
        .Cells(2, "A") = 1
        .Range(.Cells(2, "A"), .Cells(lr, "A")).DataSeries _
            Rowcol:=xlColumns, Type:=xlLinear, Step:=1
    End With

    'if the series is to be linear but not 1, 2, 3, etc then supply both
    ' the first and last value and let .Series fill in the difference
    With Worksheets("Sheet2")   '<~~ you should always know what worksheet you are on
        lr = .Cells(Rows.Count, "B").End(xlUp).Row
        .Cells(2, "A") = 1
        .Cells(lr, "A") = 99
        .Range(.Cells(2, "A"), .Cells(lr, "A")).DataSeries _
            Rowcol:=xlColumns, Type:=xlLinear, _
            Step:=(.Cells(lr, "A").Value2 - .Cells(2, "A").Value2) / (lr - 2)
    End With


End Sub

The first example is a strictly 1, 2, 3 ordinal. The latter is a relative series fill where the first and last value and known but not necessarily the number of rows rows in between.

Upvotes: 1

Paolo Medina
Paolo Medina

Reputation: 301

Never mind, I find the solution and I'll it share it for others who needs it also:

Sub FillSerialNumbers()
  With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .Cells(1, 1).Value = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
  End With
End Sub

Upvotes: 1

Related Questions