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

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

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

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