Macro for Fill Blank Cells Macro

[john-lau]

Hello

I searched the macro for fill blank cells . May I know how to modified th
following macro, therefore, it can apply on column B, C and E? Thank


Sub FillColBlanks(
'by Dave Peterson 2004-01-0
'fill blank cells in column with value abov
'http://www.contextures.com/xlDataEntry02.htm
Dim wks As Workshee
Dim rng As Rang
Dim LastRow As Lon
Dim col As Lon

Set wks = ActiveShee
With wk
col = activecell.colum
'o
'col = .range("b1").colum

Set rng = .UsedRange 'try to reset the lastcel
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Ro
Set rng = Nothin
On Error Resume Nex
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col))
.Cells.SpecialCells(xlCellTypeBlanks
On Error GoTo

If rng Is Nothing The
MsgBox "No blanks found
Exit Su
Els
rng.FormulaR1C1 = "=R[-1]C
End I

'replace formulas with value
With .Cells(1, col).EntireColum
.Value = .Valu
End Wit

End Wit

End Sub

 

[Rick Rothstein]

I searched the macro for fill blank cells . May I know how to

modified the following macro, therefore, it can apply on
column B, C and E? Thanks

<<<code snipped>>>

Does this code do what you want...

Sub FillColBlanks()
Dim Col As Variant, Data As Range, A As Range
For Each Col In Array("B", "C", "E")
Set Data = Range(Cells(1, Col), Cells(Rows.Count, Col).End(xlUp))
For Each A In Data.SpecialCells(xlCellTypeBlanks).Areas
A.Value = A(1).Offset(-1).Value
Next
Next
End Sub

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

Sorry, I forgot to include the error trapping. Use this code instead...

Sub FillColBlanks()
Dim Col As Variant, Data As Range, A As Range
On Error GoTo NoCellsFound
For Each Col In Array("B", "C", "E")
Set Data = Range(Cells(1, Col), Cells(Rows.Count, Col).End(xlUp))
For Each A In Data.SpecialCells(xlCellTypeBlanks).Areas
A.Value = A(1).Offset(-1).Value
Next
Next
NoCellsFound:
End Sub

Rick Rothstein (MVP - Excel)