Automating Fill series Function Multiple times ?
 

I hope someone can help me out.
I have a column of data with blank gaps for example somthing like the
following

15
46
55

32
21


48
59

I can manual select the value before the gaps/blank cells to the value after
the gaps so that they are highlighted and just do a Fill series function and
excel atuomaticly will interpolate values for the gaps. However I have a
rather large data set with lots of gaps and it is not very practical to do
this manualy for each gap in the column of data. I am have been trying to get
excell to automaticly do this for all the gaps, however have not found a
method to do this.

Any suggestions or help ?

Dan.

Automating Fill series Function Multiple times ?
 

Hi Dan;

You can use a sub like this


sub try()

'Grab the address for where I am
'this should be the top cell of the column
'you want to select

CellToReturnTo = ActiveCell.Address

Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
MyLastRow = ActiveCell.Row

Range(CellToReturnTo).Select

Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells
(MyLastRow, ActiveCell.Column)).Select

End Sub


Thanks,

Greg


-----Original Message-----
I hope someone can help me out.
I have a column of data with blank gaps for example
somthing like the
following

15
46
55

32
21


48
59

I can manual select the value before the gaps/blank cells
to the value after
the gaps so that they are highlighted and just do a Fill
series function and
excel atuomaticly will interpolate values for the gaps.
However I have a
rather large data set with lots of gaps and it is not
very practical to do
this manualy for each gap in the column of data. I am
have been trying to get
excell to automaticly do this for all the gaps, however
have not found a
method to do this.

Any suggestions or help ?

Dan.
.

Automating Fill series Function Multiple times ?
 

I appreciate your input and help but that is not quite what I was after.
here is what the code looks like when I preformed the oporation on 3 gaps
and recorded the macro.

Sub Macro1()
Selection.End(xlDown).Select
Range("B25:B27").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=-0.959999999999997, Trend:=False
Selection.End(xlDown).Select
Range("B66:B68").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=0.315000000000001, Trend:=False
Selection.End(xlDown).Select
Range("B96:B98").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=-0.515000000000001, Trend:=False
End Sub

I need a way to automate this type of procedure. Somthing that will find the
gaps then interpolate values for gaps between the value just before the gap
and the value just after the gap and than continue finding and intorpolating
this process for the rest of the gaps.....?


"GJones" wrote:

Hi Dan;

You can use a sub like this


sub try()

'Grab the address for where I am
'this should be the top cell of the column
'you want to select

CellToReturnTo = ActiveCell.Address

Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
MyLastRow = ActiveCell.Row

Range(CellToReturnTo).Select

Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells
(MyLastRow, ActiveCell.Column)).Select

End Sub


Thanks,

Greg


-----Original Message-----
I hope someone can help me out.
I have a column of data with blank gaps for example
somthing like the
following

15
46
55

32
21


48
59

I can manual select the value before the gaps/blank cells
to the value after
the gaps so that they are highlighted and just do a Fill
series function and
excel atuomaticly will interpolate values for the gaps.
However I have a
rather large data set with lots of gaps and it is not
very practical to do
this manualy for each gap in the column of data. I am
have been trying to get
excell to automaticly do this for all the gaps, however
have not found a
method to do this.

Any suggestions or help ?

Dan.
.

Automating Fill series Function Multiple times ?
 

But I don't think you gave enough information on how you fill the series.

If I just use the linear fill based on the top cell and bottom cell, then this
worked ok for me:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim myArea As Range
Dim myExtendedArea As Range

With Worksheets("sheet1")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) _
.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no gaps!"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
Set myExtendedArea = .Cells(1).Offset(-1, 0) _
.Resize(.Cells.Count + 2)
End With
With myExtendedArea
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=((.Cells(.Cells.Count).Value - .Cells(1).Value) _
/ (.Cells.Count - 1)), _
Trend:=False
End With
Next myArea
End With

End Sub


Dan Thompson wrote:

I hope someone can help me out.
I have a column of data with blank gaps for example somthing like the
following

15
46
55

32
21

48
59

I can manual select the value before the gaps/blank cells to the value after
the gaps so that they are highlighted and just do a Fill series function and
excel atuomaticly will interpolate values for the gaps. However I have a
rather large data set with lots of gaps and it is not very practical to do
this manualy for each gap in the column of data. I am have been trying to get
excell to automaticly do this for all the gaps, however have not found a
method to do this.

Any suggestions or help ?

Dan.

--

Dave Peterson