Hi,
I have a range of rows with a column with a formula I populated using ctrl-D. How can I insert a new line so values are blank but formulas are copied?
Thank you
Hi,
I have a range of rows with a column with a formula I populated using ctrl-D. How can I insert a new line so values are blank but formulas are copied?
Thank you
It sounds like you are looking for a "pastespecial--> formulas but not values" option. I'm pretty sure that isn't possible unless you use VBA.
You could try something like:
This will clear any cells that don't have formulas. It assumes you are in the left-most cell in the row when you call it, and that the row doesn't have any blank cells. If those assumptions aren't accurate, you'll need to tweak it to get it to work.
Sub KeepFormulasInRow()
Do Until ActiveCell.Value = ""
If Left(ActiveCell.Formula, 1) <> "=" Then
ActiveCell.Clear
End If
ActiveCell.Offset(, 1).Select
Loop
End Sub
Add the code to your workbook:
Associate it with a button:
Associate it with a shortcut: