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. 


Option ExplicitSub 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
 End Sub