Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort data without formulas in other cells changing? | Excel Discussion (Misc queries) | |||
In Bar Chart, can we display both figures and their respective %a. | Excel Discussion (Misc queries) | |||
Matrix multiplication with empty cells | Excel Worksheet Functions | |||
Getting rid of empty cells | Excel Discussion (Misc queries) | |||
Importing Data Into Formula Based Cells | Excel Worksheet Functions |