Copy cell value into blank cells below (full worksheet)
 

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.

Copy cell value into blank cells below (full worksheet)
 

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


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

Copy cell value into blank cells below (full worksheet)
 

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


"Bernie Deitrick" wrote:

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


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

Copy cell value into blank cells below (full worksheet)
 

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

Sub FillBlanks2()
Range("A2").CurrentRegion.SpecialCells(xlCellTypeB lanks).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


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


"Bernie Deitrick" wrote:

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


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

Copy cell value into blank cells below (full worksheet)
 

Hi Bernie,

I tried the command -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.

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

"Bernie Deitrick" wrote:

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

Sub FillBlanks2()
Range("A2").CurrentRegion.SpecialCells(xlCellTypeB lanks).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


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


"Bernie Deitrick" wrote:

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


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