If the cells are formatted as Text, you'll see this.

But you can format it as General (or anything else but Text):

This:
Else
Rng.FormulaR1C1 = "=R[-1]C"

Becomes:

Else
Rng.Numberformat = "General"
Rng.FormulaR1C1 = "=R[-1]C"

scorpiorc wrote:

This macro is not working. It does fill all the blank cells, but does not
copy the value from the first filled cell above the blank. Instead it puts
the text =R[-1]C in every blank cell.

"Gord Dibben" wrote:

You can do this manually if you wish.

Select column A and F5SpecialBlanksOK

In the active cell enter an = sign then point to cell above and hit CTRL +
ENTER.

That will fill all blanks with the value above them.

With column A still selected, copypaste specialvaluesokesc.

If you want a macro...............

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 MS Excel MVP

On Wed, 6 Dec 2006 08:34:00 -0800, scorpiorc
wrote:

My column looks like this, I have data in D1, then cells D2:D10 are blank,
then data in D11 and so on. The data is spaced throughout the column
(ie...not always the same # of blank cells between data). Also, the last
cell in the range can vary from week to week. I need to copy the data cell
to all blank cells below it until I get to the next nonblank cell. So if I
have "A" in D1, "B" in D11 and cells D2:D10 are blank, I need to copy "A"
into D2:D10, and then B into D12:D? (however many blank cells are below until
the next cell with data).

I have written something like this to work on a similar worksheet I have
where I have If c = "_"

lr = Cells(Rows.Count, "D").End(xlUp).Row
For Each c In Range("D1:D" & lr)
If c = "" Then c.Value = c.Offset(-1)
Next

but it does not work with blank cells in this worksheet when I change the
statement to be If c = ""
"Dave Peterson" wrote:

This is usually a very bad idea--to make cells look empty by putting a space
character in them. I wouldn't do it.

For Each z In Range("D1:D???")
If z.Value = "" Then
z = " "
End If
Next z

In fact, I'd get rid of those space character cells:

For Each z In Range("D1:D????")
If trim(z.Value) = "" Then
z.value = ""
End If
Next z



scorpiorc wrote:

I have column D that contains both data and blank cells. I need to find a
way to find the blank cells and fill them with a space. I've tried:

For Each z In Range("D1:D")
If z.Value = IsEmpty("D") Then
z = " "
End If
Next z

but get an error. Any ideas how I can do this?

--

Dave Peterson




--

Dave Peterson