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

Why don't you show us what you've recorded? Everything from the macro recorder *is* hard-coded, but generally it can be modified pretty easily... — David Zemens, Oct 27 '14 at 15:32
What do the Step values represent? If you can determine how they are assigned, it should be easy to put this in a loop. — David Zemens, Oct 28 '14 at 13:55
Thanks @David Zemens. It appears that the step value is the difference between the first value selected and the next value I selected, divided by the number of blank cells in between(plus 1). For example the first value I have selected in the macro above was 894310 and the next value was 918000. The difference is 23690, which divided by 2 is 11845(the step value shown in the macro) where there was one blank cell in between. I've tried the same with four blank cells between and the divisor was 5. Hope that makes sense? — jellybean1977, Oct 28 '14 at 16:52
Kind of, but it doesn't really follow or make sense based on the example data you have shown in your question. I don't see anything like 894310 in that small data table you have. It is hard to understand what you're actually doing. — David Zemens, Oct 28 '14 at 16:59
Also, assuming I can put this in a loop, what is the condition which will cause it to "stop"? — David Zemens, Oct 28 '14 at 17:11
Sorry, the example data really was just an example. I didn't know an easy way to paste actual figures from excel. To help make sense of it lets say in the example, Male2013=894310 and Male2015=918000 and the one cell between them (Male2014) is blank. Thanks J — jellybean1977, Oct 28 '14 at 17:14
ok that's fine. Need to know when to "stop" then, will it be a blank cell in column A that will trigger the "stop"? — David Zemens, Oct 28 '14 at 17:16

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

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

edited May 23 '17 at 10:33

Community

1
1

answered Oct 28 '14 at 17:18

David Zemens

53,033
11
81
130

Thanks @David Zemens. That is working in terms of the loop, but the step value calculation isn't quite right. It appears to be a) not dividing by the right amount(e.g. if four blank cells between, then it should divide by 5 rather than dividing by the number of blanks, then adding one to that total) and b) it overwrites the rightmost value in the range, rather than just filling the blanks between? Thanks J – jellybean1977 Oct 29 '14 at 09:29
I think some parentheses in the `stepValue` calculation will help the first issue. put the denominator `rng.SpecialCells(xlCellTypeBlanks).Count +1` in parentheses. – David Zemens Oct 29 '14 at 14:08
For second problem, do `rng.Resize(, rng.Cells.Count -1).DataSeries ... ` – David Zemens Oct 29 '14 at 14:10
I've updated my answer with both of these revisions. – David Zemens Oct 29 '14 at 14:11
Thanks @David Zemens. It's working perfectly now. Thank you so much. That has saved me hours of manual filling. J – jellybean1977 Oct 29 '14 at 17:31

Excel Macro for Linear Series Fill

1 Answers1