cassid -
Dec 5, 2008 at 11:24 AM - Latest reply: dirtydan
- May 10, 2010 at 06:28 AM

- Excel vb find blank cells replace with calc
- Excel - Find & replace blank cells in Excel » How-To - Excel
- Formula to Find&replace blank cells in excel [Solved] (Solved) » Forum - Programming
- Excel - A Macro to copy and paste data in next blank cell » How-To - Excel
- Excel/VBA - Get the row number of the first blank cell » How-To - Excel
- Excel autofilter stops filtering [Solved] (Solved) » Forum - Excel

Best 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

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

Thanks for this have use the same code

to average gaps in weekly data

by averaging the columns rather than the rows

Burly