CODEWITHSUNDEEP
excelvbaautofill
AnnaNmty
AnnaNmty

Reputation: 13

Excel VBA AutoFill dynamic range and increment by 10

Is there a way to have AutoFill increment by 10 instead of 1? The following code functions in my worksheet. The user (geologist) defines a beginning station number in B1 and an end station number in B2. The macro is run and beginning with C5, the column is populated with station numbers from the beginning to the end station. However, data only needs to be collected every 10 stations, and I have not been able to figure out how to incorporate that into my code. Instead of 1000, 1001, 1002...etc., I would like 1000, 1010, 1020...

Sub StationFill()

Dim taskStationEnd As Long
Dim taskStationBegin As Long

taskStationBegin = Range("B1").Value
taskStationEnd = Range("B2").Value

With Worksheets("Data")
    .Columns(3).ClearContents
    Set SourceRange = .Range("C5")
    SourceRange.Value = taskStationBegin
    Set fillRange = .Range(SourceRange, _
      Cells(SourceRange.Row + taskStationEnd - taskStationBegin, SourceRange.Column))
    SourceRange.AutoFill Destination:=fillRange, Type:=xlFillSeries
End With

End Sub

(First time poster. Not sure I formatted correctly and got the code line breaks in the right place)

Upvotes: 1

Views: 1561

Answers (2)

Christof S
Christof S

Reputation: 1

I've tried with Excel 2010 and there is a increment value available (step).

Range("A1:A26").DataSeries Rowcol:=xlColumns, Type:=xlLinear, **Step:=10**

Maybe it helps you.

Kind regards, Christof

Upvotes: 0

L42
L42

Reputation: 19737

Try this:

Sub marine()
    Dim taskStationEnd As Long
    Dim taskStationBegin As Long
    Dim SourceRange As Range

    Const interval As Long = 10 '<~~ this is your interval

    With Sheets("Sheet1") '<~~ change to suit
        taskStationBegin = .Range("B1").Value
        taskStationEnd = .Range("B2").Value
        Set SourceRange = .Range("C5")
        SourceRange = taskStationBegin
        SourceRange.Offset(1, 0) = taskStationBegin + interval
        SourceRange.Resize(2).AutoFill _
            SourceRange.Resize(((taskStationEnd - taskStationBegin) / interval) + 1) _
            , xlFillSeries
    End With
End Sub

Upvotes: 1

Related Questions