data in empty cells
 

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?

Manual method......

Select the column then F5SpecialBlanksOK.

Enter an = sign in top blank cell then point to cell above.

Hit CRTL + ENTER to replicate down the column.

When happy, copypaste specialvaluesOKEsc.

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

On Wed, 30 Mar 2005 09:27:09 -0800, "samsmimi"
wrote:

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?

Thanks so much for your help!

"Gord Dibben" wrote:

Manual method......

Select the column then F5SpecialBlanksOK.

Enter an = sign in top blank cell then point to cell above.

Hit CRTL + ENTER to replicate down the column.

When happy, copypaste specialvaluesOKEsc.

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

On Wed, 30 Mar 2005 09:27:09 -0800, "samsmimi"
wrote:

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?

You're welcome.

Thanks for the feedback.


Gord

On Thu, 31 Mar 2005 07:23:07 -0800, "samsmimi"
wrote:

Thanks so much for your help!

"Gord Dibben" wrote:

Manual method......

Select the column then F5SpecialBlanksOK.

Enter an = sign in top blank cell then point to cell above.

Hit CRTL + ENTER to replicate down the column.

When happy, copypaste specialvaluesOKEsc.

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

On Wed, 30 Mar 2005 09:27:09 -0800, "samsmimi"
wrote:

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?