I have a list of data, fill in the gaps. FILL function won't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of data in which I would like to fill in the gaps. Is this
possiable?

I have age in years in A2 to A100 then data in colums in 5 year gaps (eg B2
has data then B7 then B12 etc) all incremental. I would like to fill in the
gaps belween giving me data B2, B3, B4... to B100.

How can I fill in the gaps whilst still making sure my 5 year results are
left.


Simple example below
age Result
45 20
46
47
48
49
50 30
51
52
53
54
55 50


Many Thanks for any help
 
You could select a range (B2:B7 for 20 to 30).

Then do Edit|fill|series
There's an option in there for linear.

Or you could click the Trend box and use Linear or Growth.

This does the a linear version for each step in code:

Option Explicit

Sub testme()
Dim TopCell As Range
Dim BotCell As Range
Dim FirstRow As Long
Dim myStep As Double

With ActiveSheet
FirstRow = 2

Set BotCell = .Cells(.Rows.Count, "B").End(xlUp)

Do
Set TopCell = BotCell.End(xlUp)

myStep = (BotCell.Value - TopCell.Value) _
/ (BotCell.Row - TopCell.Row)

With .Range(TopCell, BotCell)
.DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, Step:=myStep, _
Trend:=False
End With

Set BotCell = TopCell
If BotCell.Row <= FirstRow Then
Exit Do
End If
Loop
End With

End Sub

It does assume that you don't have any values in column B that are
adjacent--there always has to be a gap.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top