A spate of geekery.

[nonethefewer]

I sent this in response to a question sent in an Excel Tips newsletter.  I figured I'd post it here, since I know some people use Excel, and because I want to save it for later.

–

"At the beginning of each year I make a copy of the previous year's Excel workbook. I then need to delete everything in the copy except for formulas. Is there a way to do this easily?"

One way is to protect the cells with formulas, then just run a macro that deletes every cell that isn't protected.  I don't know the best way, but here's what I found:

Sub DeleteFree()

ActiveSheet.Unprotect
Application.ScreenUpdating = False

strRange = ActiveCell.SpecialCells(xlLastCell).Address(False, False)

intCheck = Val(Mid(strRange, 2, 1))
If intCheck = 0 Then intCol = 2 Else intCol = 1
intRows = Val(Right(strRange, Len(strRange) – intCol))
strCol = Left(strRange, intCol)

For x = 1 To intRows
y = 0
Do
y = y + 1
If Cells(x, y).Locked = True Then Cells(x, y) = ""
Loop Until UCase(Left(Cells(x, y).Address(False, False), intCol)) = strCol
Next

Application.ScreenUpdating = True

This assumes that you first go through and unlock all cells, then lock the ones with formulas.  You can do this by selecting the cells, going to Format > Cells, clicking on the Protection tab, and toggling 'Locked'.

Originally posted at Xtinian Thoughts.  Comment here or there.