This post will guide you how to fill blank cells in a range with linear values in Excel. How do I fill up empty cells by linear interpolation in Excel 2013/2016.

1. Fill Blank Cells with Linear Values Using Excel’s Fill Feature

Assuming that you have a blank cells in a range (B1:G1), and you want to fill blank cells with linear values. How to do it. You can do it by the following steps:

#1 select range B1:G1 in your worksheet

#2 go to HOME tab, click Fill command under Editing group. And select Series from the drop down menu list. And the Series dialog will open.

#3 select Linear option under Type section, and enter one step value that you want to use. Click Ok button.

#4 you should see that blank cells have been filled up with linear values in your selected range of cells.

2. Fill Blank Cells with Linear Values Using VBA Code

For our second method, we’ll delve into the world of VBA with a custom macro. This approach offers greater flexibility and can be tailored to more complex scenarios where you need to fill blank cells with linear values based on specific conditions.

To start, press ALT + F11 to open the Visual Basic for Applications editor. This integrated development environment allows you to write and run VBA code to automate tasks in Excel.

In the VBA editor, insert a new module by going to Insert and select Module.

Here, you’ll write a macro that loops through each cell in your selected range. The macro will check for blank cells and then calculate the appropriate linear value to fill them, based on the surrounding cells’ values.

Copy and paste the provided VBA code into the new module.

Sub FillBlanksWithLinearValues()
    Dim startValue As Double
    Dim endValue As Double
    Dim stepValue As Double
    Dim currentValue As Double
    Dim targetRange As Range
    Dim cell As Range

    ' Prompt user to select the start value cell
    Set targetRange = Application.InputBox("Select the cell with the start value", "Fill Blank Cells", Type:=8)

    ' Prompt user to select the end value cell
    'Set targetRange = Application.InputBox("Select the cell with the end value", "Fill Blank Cells", Type:=8)

    ' Prompt user to enter the step value
    stepValue = Application.InputBox("Enter the step value for linear progression", "Fill Blank Cells", Type:=1)
    
    ' Check if stepValue is zero or negative
    If stepValue <= 0 Then
        MsgBox "Step value must be greater than zero.", vbExclamation
        Exit Sub
    End If
    
    ' Set the start value
    startValue = targetRange.Value
    
    ' Initialize currentValue with the start value
    currentValue = startValue
    
    ' Prompt user to select the range to fill
    Set targetRange = Application.InputBox("Select the range to fill with linear values", "Fill Blank Cells", Type:=8)
    
    ' Loop through each cell in the selected range
    For Each cell In targetRange
        If IsEmpty(cell.Value) Then
        ' Increment the current value by the step value
            currentValue = currentValue + stepValue
            ' Fill the blank cell with the current value
            cell.Value = currentValue
            
        End If
    Next cell
End Sub

Close the VBA editor and return to Excel.

Press ALT + F8, select FillBlanksWithLinearValues from the list, and click Run.

Follow the prompts in the InputBox dialogs to select the start value cell, and enter the step value.

After entering the required information, select the range of cells where you want to fill in the linear values.

The macro will fill the selected range with linear values starting from the start value, incrementing by the step value until it reaches or surpasses the end value.

3. Video: Fill Blank Cells with Linear Values

This Excel video tutorial, we’ll explore two methods(Excel’s Fill Feature and VBA Code) to fill blank cells with linear values. These techniques are incredibly useful for creating sequences, interpolating data, and ensuring the continuity of your datasets.

https://youtu.be/N9XV6cwL3cA