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?

Give this a whirl...

Sub FillBlanks()
Dim rng As Range

On Error Resume Next
Set rng = Columns("D").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then rng.Value = " "

End Sub

--
HTH...

Jim Thomlinson


"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?

You didn't specify the end of your range. Also, you needed to use z.Value
on both lines. IsBlank is not really necessary here. Try this:

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


Regards,
Paul


"scorpiorc" wrote in message
...
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?

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

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

How about something like:

For Each z In Range("D1:D" & Range("D65536").End(xlUp).Row)
If z.Value = "" Then
z.Value = " "
End If
Next z

Regards,
Paul

"scorpiorc" wrote in message
...
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

So you want to fill an empty cell with the value of the cell above it (that's
already filled)?

If this is what you want, visit Debra Dalgleish's site for some tips--code and
manual:
http://contextures.com/xlDataEntry02.html

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

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

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

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

Thanks for update Dave.

Have amended my version.


Gord

On Thu, 07 Dec 2006 11:06:25 -0600, Dave Peterson
wrote:

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




Gord Dibben MS Excel MVP