VBA - Filling in blanks across multple worksheets

Morgan - Sep 14, 2016 at 02:31 PM

I currently have an excel file containing multiple worksheets. Rather than filling in blanks using the same value above is it possible to enter in a specified value such as 0 or x. Also instead of having to run the code on each sheet, is it possible to run the code once for the entire workbook?

Sub FillColBlanksSpecial()

Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long

Dim lCount As Long
On Error Resume Next

lRows = 2 'starting row
lLimit = 8000

Set wks = ActiveSheet
With wks
col = ActiveCell.Column

Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing

lCount = .Columns(col).SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count

If lCount = 0 Then
MsgBox "No blanks found in selected column"
Exit Sub
ElseIf lCount = .Columns(col).Cells.Count Then
MsgBox "Over the Special Cells Limit" 'this line can be deleted
Do While lRows < LastRow
Set rng = .Range(.Cells(lRows, col), .Cells(lRows + lLimit, col)) _
rng.FormulaR1C1 = "=R[-1]C"
lRows = lRows + lLimit
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With

End With

End Sub