empty cell = above cell
 

Hi,
I have pivot tables that I need to convert into plain columns. So I copy and
paste the values into a new worksheet. Is there a way that I can (with a
macro) fill in the blank cells with the value located in the cell right
above? Keeping in mind that the cell length will vary...
Thanks in advance!
Géraldine

empty cell = above cell
 

Hi Géraldine,

If you need to do this programmatiacally, try :

Sub Tester()
Dim rng As Range

Set rmg = Range("A1:B20") '<<========== CHANGE
With rng
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "=R[-1]C"
.Value = .Value
End With

End Sub


---
Regards,
Norman



"Kanga" wrote in message
...
Hi,
I have pivot tables that I need to convert into plain columns. So I copy
and
paste the values into a new worksheet. Is there a way that I can (with a
macro) fill in the blank cells with the value located in the cell right
above? Keeping in mind that the cell length will vary...
Thanks in advance!
Géraldine

empty cell = above cell
 

Hi Géraldine,

To avoid problems which would be caused if no blank cells were found or,
alternatively, the range comprised more than 8192 non-contiguous blank cell
areas, an error handler should be used:

:Sub Tester1A()
Dim rng As Range

Set rng = Range("A1:B20") '<<========== CHANGE
With rng
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "=R[-1]C"
On Error GoTo 0
.Value = .Value
End With

End Sub

Note, also, a typo in my previous response:

Set rmg = Range("A1:B20")

should have been:

Set rng = Range("A1:B20")



---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Géraldine,

If you need to do this programmatiacally, try :

Sub Tester()
Dim rng As Range

Set rmg = Range("A1:B20") '<<========== CHANGE
With rng
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "=R[-1]C"
.Value = .Value
End With

End Sub


---
Regards,
Norman



"Kanga" wrote in message
...
Hi,
I have pivot tables that I need to convert into plain columns. So I copy
and
paste the values into a new worksheet. Is there a way that I can (with a
macro) fill in the blank cells with the value located in the cell right
above? Keeping in mind that the cell length will vary...
Thanks in advance!
Géraldine

empty cell = above cell
 

Thank you! This was great help you have no idea!
Kanga

"Norman Jones" wrote:

Hi Géraldine,

To avoid problems which would be caused if no blank cells were found or,
alternatively, the range comprised more than 8192 non-contiguous blank cell
areas, an error handler should be used:

:Sub Tester1A()
Dim rng As Range

Set rng = Range("A1:B20") '<<========== CHANGE
With rng
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "=R[-1]C"
On Error GoTo 0
.Value = .Value
End With

End Sub

Note, also, a typo in my previous response:

Set rmg = Range("A1:B20")

should have been:

Set rng = Range("A1:B20")



---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Géraldine,

If you need to do this programmatiacally, try :

Sub Tester()
Dim rng As Range

Set rmg = Range("A1:B20") '<<========== CHANGE
With rng
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "=R[-1]C"
.Value = .Value
End With

End Sub


---
Regards,
Norman



"Kanga" wrote in message
...
Hi,
I have pivot tables that I need to convert into plain columns. So I copy
and
paste the values into a new worksheet. Is there a way that I can (with a
macro) fill in the blank cells with the value located in the cell right
above? Keeping in mind that the cell length will vary...
Thanks in advance!
Géraldine