Copy cell value into blank cells below (full worksheet)

[SimmoG]

If I copy a pivot table to another worksheet I have loads of blanks in
columns e.g
75 Total BLANK BLANK
90 1-IDUGB 23/04/2008
BLANK BLANK BLANK
BLANK 1-IFIG9 17/12/2007
BLANK 1-IM4OK 29/01/2008

There is a trick to copy the above cell value to the Blank cell below using
Edit then Go To Blanks.. but i cannot remember the key strokes to complete it.

 

[Bernie Deitrick]

Select all cells (except for row 1) then use Edit Go To.. Special Blanks Ok, then type = and
press up arrow once, then Ctrl-Enter.

 

[ryguy7272]

Bernie is right! You can also run a macro like this:
Sub FillBlanks()
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3:A30").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

....change the ranges to suit your needs.


Regards,
Ryan---

 

[Bernie Deitrick]

As long as there are no fully blank rows or columns, but you know you have some blanks:

Sub FillBlanks2()
Range("A2").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub

As a utility:

Sub FillBlanksWithFormulas()
On Error GoTo NoBlanks:
With ActiveCell.CurrentRegion
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End With
NoBlanks:
End Sub

OR

Sub FillBlanksWithValues()
On Error GoTo NoBlanks:
With ActiveCell.CurrentRegion
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Cells.Value = Cells.Value
End With
NoBlanks:
End Sub



HTH,
Bernie
MS Excel MVP

Bernie is right! You can also run a macro like this:
Sub FillBlanks()
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3:A30").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

...change the ranges to suit your needs.


Regards,
Ryan---

--
RyGuy

Select all cells (except for row 1) then use Edit Go To.. Special Blanks Ok, then type = and
press up arrow once, then Ctrl-Enter.

 

[Fesco]

Hi Bernie,

I tried the command -Select all cells (except for row 1) then use Edit Go
To.. Special Blanks Ok, then type = and
I am trying to copy the last cell on top but the above command is only
copying the A2 and giving me "=A2" and not the text (name) on the last cell.

I have different text on the rows ..say A1 has George and A4 has Jimmy so i
want to copy George to A3 and Jimmy to A5.

Thanks,

Jina

As long as there are no fully blank rows or columns, but you know you have some blanks:

Sub FillBlanks2()
Range("A2").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub

As a utility:

Sub FillBlanksWithFormulas()
On Error GoTo NoBlanks:
With ActiveCell.CurrentRegion
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End With
NoBlanks:
End Sub

OR

Sub FillBlanksWithValues()
On Error GoTo NoBlanks:
With ActiveCell.CurrentRegion
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Cells.Value = Cells.Value
End With
NoBlanks:
End Sub



HTH,
Bernie
MS Excel MVP

Bernie is right! You can also run a macro like this:
Sub FillBlanks()
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3:A30").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

...change the ranges to suit your needs.


Regards,
Ryan---

--
RyGuy

Select all cells (except for row 1) then use Edit Go To.. Special Blanks Ok, then type = and
press up arrow once, then Ctrl-Enter.

--
HTH,
Bernie
MS Excel MVP


If I copy a pivot table to another worksheet I have loads of blanks in
columns e.g
75 Total BLANK BLANK
90 1-IDUGB 23/04/2008
BLANK BLANK BLANK
BLANK 1-IFIG9 17/12/2007
BLANK 1-IM4OK 29/01/2008

There is a trick to copy the above cell value to the Blank cell below using
Edit then Go To Blanks.. but i cannot remember the key strokes to complete it.