fill in blank cell

  • Thread starter Thread starter Cindy Wang
  • Start date Start date
C

Cindy Wang

Is there anyway that you can fill in the blanks with multiple
sheets? I understand that I can use go to special and choose blank
to fill the blanks, but is there a way that I can fill multiple sheets
all in once? Anybody has a macro like that? Thanks!
 
Give us an idea of what columns or ranges would be involved on each of
the multiple sheets.

What would you fill the blanks with? The value above? Or something
else?

This macro from Dave Peterson......revised by me to loop through all
sheets will fill in the blanks in Column A with the value from the
cell above.

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim lastrow As Long
Dim Col As Long

For Each wks In ActiveWorkbook.Worksheets
With wks
Col = .Range("A1").Column

Set rng = .UsedRange 'try to reset the lastcell
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, Col), .Cells(lastrow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.NumberFormat = "General"
rng.FormulaR1C1 = "=R[-1]C"
End If

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

End With
Next wks
End Sub


Gord
 
Back
Top