Fill data series or resize range not working on large blank ranges

Morning

I have an inherited macro, that occasionally does not behave as intended, when trying to fill a data series between two column values - sometimes when there is a large number of blank cells/rows between the two points, it doesn't fill correctly.

So it has a user entered data fields on the first 4 columns - the fourth column is a marker used to indicate certain triggers IRL and used as targets in this macro

so in this example, it has an 's' marker that the macro looks for to determine the start and end of the data series fill - note that there may be several different markers throughout the data series, each 'pair' it calculates a new series for, and fills between. it also uses the first and last cells by default.

So I have stripped some other stuff out of this to redact it slightly, but the example shows a failure state, when running the 'get values' macro button:
1) it is able to fill a data series in column L between the 's' tags at row 2 and 21
2) it fails to enter a data series between the next set, between row 21 and row 18116

I can manually create a FILL / SERIES / COLUMN / LINEAR / TREND between these samples

You can also step through the 'get_overwrite_Original' macro, and see that it is calculating the correct step value (I think)

So, the macro works when there is a small number of cells to range over, but fails if this is too large - I have been unable to find the information about limiting the resize range command it seems to use - any ideas?

Example excel, with sample data and macro attached - click clear calculated field, then get values to see in action

Gareth

Afternoon

Unfortunately the code did not work for me, in my situation

However, a bit more digging found a different way to do it:

'fill data series column
Application.ScreenUpdating = True

Dim sht As Worksheet
Dim firstCell As Range, lastCell As Range
Dim fillRange As Range, cell As Range
Dim lastRow As Long, firstRow As Long
Dim fillCol As String
Set sht = ThisWorkbook.Worksheets("raw data")
lastRow = lr 'the row of the last cell you want to fill
firstRow = 2 'the row where your data begins
fillCol = "L" 'the column to be filled


Set firstCell = sht.Range(fillCol & firstRow)
While firstCell.Row <> lastRow
    If firstCell.End(xlDown).Row > lastRow Then
        Set lastCell = sht.Range(fillCol & lastRow)
    Else
        Set lastCell = firstCell.End(xlDown)
    End If
    sht.Range(firstCell, lastCell).DataSeries Type:=xlLinear, Trend:=True
    Set firstCell = lastCell
Wend

in this example, my last line is LR in initial macro,or could be entered as a manual value.

the above macro used a CTRL + Down arrow type 'search' to jump between the values i want to fill between. I therefore had to change the format of my 'fill' column after a paste, and while its not the most elegant solution, I used a record macros, text to columns code to do this:

Selection.TextToColumns Destination:=Range("L2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

always fun how there are so many different ways to do things - anyways thanks for the help

Gareth

well the example I was using was heavily redacted...

it had more triggers than just the 's', and needed the first and last values highlighted as well.

as usual when I deal with programmers, it makes perfect sense in my head when I explain it the first time, but I always need to add stuff i missed out

Away to dig out my other partially broken files now

Gareth