Excel vb find blank cells replace with calc
Solved/Closed
Related:
- Find and replace blank cells in excel
- Number to words in excel - Guide
- Gif in excel - Guide
- Marksheet in excel - Guide
- How to take screenshot in excel - Guide
- Excel apk for pc - Download - Spreadsheets
1 response
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
Aug 2, 2009 at 06:53 PM
Thanks for this have use the same code
to average gaps in weekly data
by averaging the columns rather than the rows
Burly
Mar 27, 2010 at 01:10 AM
May 10, 2010 at 06:28 AM