Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
samsmimi
 
Posts: n/a
Default 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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
samsmimi
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sort data without formulas in other cells changing? Vicky Excel Discussion (Misc queries) 2 March 15th 05 02:17 PM
In Bar Chart, can we display both figures and their respective %a. Airtel Excel Discussion (Misc queries) 3 March 9th 05 03:35 PM
Matrix multiplication with empty cells Ingrid Voigt Excel Worksheet Functions 2 March 8th 05 04:47 AM
Getting rid of empty cells Jambruins Excel Discussion (Misc queries) 2 February 22nd 05 05:38 PM
Importing Data Into Formula Based Cells Heyna Excel Worksheet Functions 1 November 4th 04 07:04 PM


All times are GMT +1. The time now is 03:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"