S
samsmimi
Is there a way to program all empty cells in a column to be filled with text
copied from the cell above each empty cell?
copied from the cell above each empty cell?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gord Dibben
Manual method......
Select the column then F5>Special>Blanks>OK.
Enter an = sign in top blank cell then point to cell above.
Hit CRTL + ENTER to replicate down the column.
When happy, copy>paste special>values>OK>Esc.
VBA macro method..........
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
Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").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.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With
End With
End Sub
Gord Dibben Excel MVP
Select the column then F5>Special>Blanks>OK.
Enter an = sign in top blank cell then point to cell above.
Hit CRTL + ENTER to replicate down the column.
When happy, copy>paste special>values>OK>Esc.
VBA macro method..........
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
Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").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.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With
End With
End Sub
Gord Dibben Excel MVP
S
samsmimi
Thanks so much for your help!
Gord Dibben said:Manual method......
Select the column then F5>Special>Blanks>OK.
Enter an = sign in top blank cell then point to cell above.
Hit CRTL + ENTER to replicate down the column.
When happy, copy>paste special>values>OK>Esc.
VBA macro method..........
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
Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").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.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With
End With
End Sub
Gord Dibben Excel MVP
Is there a way to program all empty cells in a column to be filled with text
copied from the cell above each empty cell?