Excel vb find blank cells replace with calc

 dirtydan -

I'm trying to write a simple vb script in excel which will find blank cell values in a specific row, and then calculate a new value for each blank cell defined as the average of the immediate value above the empty cell, and the immediate value below the empty cell.


1 reply

Figured it out myself.....used this code:

Sub FillEmpty()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim cell As Range
For Each cell In Intersect(Selection, _
If Trim(cell) = "" And cell.Row > 1 Then
cell.NumberFormat = cell.Offset(-1, 0).NumberFormat
cell.Value = ((cell.Offset(-1, 0).Value + cell.Offset(+1, 0).Value)/2)
End If
Next cell
Application.Calculation = xlAutomatic 'xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Budget Data Cleansing

Thanks for this have use the same code
to average gaps in weekly data
by averaging the columns rather than the rows

but if there is more than one blank cell adjacent to one another this doesn't work? any suggestions on how it can be improved to overcome this?
Do you know how you would change this so that instead it would check the next column value and replace the blank with either 100 or 98.44 based on the value of that column being equal to or greater than 0?