Excel vb find blank cells replace with calc Solved/Closed

Question

Hello,

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. 

Thanks!!

cassid · Accepted Answer

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, _
            ActiveSheet.UsedRange)
    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

Excel vb find blank cells replace with calc

1 response

Similar discussions

Subscribe To Our Newsletter!