Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Data from Row 1 to last blank row in next worksheet | Excel Worksheet Functions | |||
Where are the examples from Help to copy to a blank worksheet? | Excel Discussion (Misc queries) | |||
Macro copy and paste = blank worksheet | Excel Discussion (Misc queries) | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
how to copy 2350 hyperlink full paths to any column in a worksheet ? | Excel Discussion (Misc queries) |