Excel Macro for Linear Series Fill

Question

I have a set of data in excel. Population in millions by year, however there are gaps in the data that I need to fill. See example below

Year--2013--2014--2015--2016--2017--2018--2019--2020
Male--   5--    --   7--    --    --   8--    --  10
 Fem--   4--    --   5--    --    --   7--    --   9

I know I can use Fill/Series/Linear, but I need to do this for hundreds of Countries from 2000-2050. I tried to record a macro of me doing it, but the Step Value seems to be hard coded.

Is this even possible or do I bite the bullet and continue manually?

Thanks

J

Sub FillTheGaps()
'
' fill the gaps Macro
'

'start at cell A2
'find first gap on that row
Selection.End(xlToRight).Select
'select up to and including next non-blank
Range(Selection, Selection.End(xlToRight)).Select
Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step _
    :=11845, Trend:=False
Selection.End(xlToLeft).Select
'move down to next row
ActiveCell.Offset(1).Select
Selection.End(xlToRight).Select
'select up to and including next non-blank
Range(Selection, Selection.End(xlToRight)).Select
Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step _
    :=8598, Trend:=False
Selection.End(xlToLeft).Select
'move down to next row
ActiveCell.Offset(1).Select
Selection.End(xlToRight).Select
'select up to and including next non-blank
Range(Selection, Selection.End(xlToRight)).Select
Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, Step _
    :=30400, Trend:=False
'move down to next row
ActiveCell.Offset(1).Select
End Sub

Answer 1

I think this should get you started. What I did was create a loop, and do some computation to get the step value based on your explanation, since that is really the only "variable". The rest is an exercise in how to avoid using or relying on the Selection method in Excel; I create a variable rng to represent each row/range of data, and then use the appropriate methods to define that range, rather than relying on manual user selection of the range.

Dim rng As Range
Dim stepValue As Long

Set rng = Range("A2", Range("A2").End(xlToRight))

Do
   'Compute the difference between the first & last cell in the range,
   ' divided by the number of blank cells + 1.
   stepValue = (rng(rng.Cells.Count).Value - rng(1).Value) / _
            (rng.SpecialCells(xlCellTypeBlanks).Count + 1)

   'now we can use our computed "stepValue" instead of hard-coding it as a constant:
   '## Use the resize method to avoid overwriting the last cell in this range
    rng.Resize(, rng.Cells.Count - 1).DataSeries Rowcol:=xlRows, _
               Type:=xlLinear, _
               Date:=xlDay, _
               Step:=stepValue, _
               Trend:=False


   'Increment the range to the next row 
   Set rng = Range(rng(1).Offset(1), rng(1).Offset(1).End(xlToRight))

'Escape the loop only when we reach an empty/blank cell in the first column:
Loop Until Trim(rng(1).Value) = vbNullString