Hello -
I have a complicated issue. I have a file with 5 columns (A, B, C, D, E).
A is fixed string value.
B is a number increasing irregularly.
C, D, E, are given values.
What I need to do is have column B regular (i.e. every 1). So I have to insert rows between B i.e.(B2-B1) will determine how many rows between B1 & B2 and then (B3-B2) will determine how many rows between B2 & B3 and so on.
After inserting the rows, I have to linear interpolate columns C, D & E in the newly created empty rows. In all cases I want to keep the original values and interpolate between them.
The other issue is that column B has decimal/fraction, but I think I can round this to the nearest integer to make it easier for interpolation.
[COLOR=#333333]Option Explicit[/COLOR]
Sub Test01()
Application.ScreenUpdating = False
Dim numRows As Long
Dim r As Long
Dim Rng As Range
Dim lastrw As Long
Dim Ar As Range
Dim StepValue1
Dim StepValue2
Dim StepValue3
Dim Ar1 As Range
Dim AR2 As Range
Dim i As Integer
lastrw = Cells(Rows.Count, "A").End(xlUp).Row
i = 1
For i = i + 0 To lastrw Step 1
Set Rng = Range(Cells(i, "A"), Cells(lastrw, "A"))
numRows = Cells(i + 1, 2).Value - Cells(i + 0, 2).Value
For r = Rng.Rows.Count To 1 Step -1
Rng.Rows(r + i).Resize(numRows - 1).EntireRow.Insert
Next r
Next i
Set Rng = Columns(1).SpecialCells(xlBlanks)
For Each Ar In Rng.Areas
Set Ar1 = Ar.Offset(-1, 0).Resize(Ar.Rows.Count + 1)
Set AR2 = Ar1.Resize(Ar1.Rows.Count + 1)
StepValue1 = (AR2(AR2.Count).Offset(0, 2) - _
Ar1(1).Offset(0, 2)) / Ar1.Count
StepValue2 = (AR2(AR2.Count).Offset(0, 3) - _
Ar1(1).Offset(0, 3)) / Ar1.Count
StepValue3 = (AR2(AR2.Count).Offset(0, 4) - _
Ar1(1).Offset(0, 4)) / Ar1.Count
Ar1.Offset(0, 2).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=StepValue1, Trend:=False
Ar1.Offset(0, 3).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=StepValue2, Trend:=False
Ar1.Offset(0, 4).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=StepValue3, Trend:=False
Next
[COLOR=#333333]End Sub[/COLOR][COLOR=#333333][COLOR=#333333][FONT=Arial][/FONT][/COLOR][/COLOR]Example Input: http://i65.tinypic.com/v30kz6.jpg
Desired output: http://i66.tinypic.com/2hrod4n.jpg