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

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" <Kanga@discussions.microsoft.com> wrote in message
news:43ABF422-4CF3-4126-A29D-FE2346F8E336@microsoft.com...
> 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" <normanjones@whereforartthou.com> wrote in message
news:OD8lY79nFHA.2152@TK2MSFTNGP14.phx.gbl...
> 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" <Kanga@discussions.microsoft.com> wrote in message
> news:43ABF422-4CF3-4126-A29D-FE2346F8E336@microsoft.com...
>> 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" <normanjones@whereforartthou.com> wrote in message
> news:OD8lY79nFHA.2152@TK2MSFTNGP14.phx.gbl...
> > 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" <Kanga@discussions.microsoft.com> wrote in message
> > news:43ABF422-4CF3-4126-A29D-FE2346F8E336@microsoft.com...
> >> 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
> >
> >
>
>
>