Fill down function
 

I want Excel to determine if a cell is blank, if yes then copy the cell
above, if no then move to the next cell until another blank cell is found and
then copy the cell directly above

Fill down function
 

Are we talking about a specific column or do you mean for every cell on the
sheet?

How do you determine when to stop? I mean, if the last filled row in Column
A is, say, 1000, then 1001 is blank... do you want A1000 copied into it? If
so, then what about A1002... it is blank... should the newly copied data in
A1001 be copied into it? If so, this process would contiue to the end of the
sheet. So, how do you determine when to stop?

Rick

"ambr711" wrote in message
...
I want Excel to determine if a cell is blank, if yes then copy the cell
above, if no then move to the next cell until another blank cell is found
and
then copy the cell directly above

Fill down function
 

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.NumberFormat = "General"
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 MS Excel MVP

On Fri, 14 Dec 2007 10:02:03 -0800, ambr711
wrote:

I want Excel to determine if a cell is blank, if yes then copy the cell
above, if no then move to the next cell until another blank cell is found and
then copy the cell directly above

Fill down function
 

From my long experience I'd recommend the following wooden method, which
works with the selection - it is obviously most reliable. Though it may be
easily re-built to the UsedRange or so.

PB

Sub CopyInSelect()

'Macro fills up the empty cells in the selection in such a way that it
'copies each non-empty one, beginning with the upper left corner, into the
'following ones, until it hits the next non-empty. Then the same process
'repeats again. If the number of columns in the selection is
'greater or equal to the number of rows, the copying runs from up to down,
'and vice versa from left to right.
'Petr Bezucha, 2002

Dim I As Long, R1 As Long, R2 As Long
Dim J As Long, C1 As Long, C2 As Long
With Selection
R1 = .Row
R2 = .Rows.Count
C1 = .Column
C2 = .Columns.Count
End With
S = ""
If R2 < C2 Then
For I = R1 To R2
For J = C1 To C2
GoSub Action
Next J
Next I
Else
For J = C1 To C1 + C2 - 1
For I = R1 To R1 + R2 - 1
GoSub Action
Next I
Next J
End If
Exit Sub
Action:
Set C = Cells(I, J)
If IsEmpty(C) Then
C.Value = S
Else
S = C.Value
End If
Return
End Sub

--
Petr Bezucha


"ambr711" wrote:

I want Excel to determine if a cell is blank, if yes then copy the cell
above, if no then move to the next cell until another blank cell is found and
then copy the cell directly above