fill in blank cell

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!
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top