Refresh - F2

[enna49]

You are probably tired of this type of question, but here goes.

Is there any way to quickly go through a spreadsheet with the

F2 Enter (to refresh a cell) I have tried a Macro, as well as the copy
and paste special with the Multiply which works for format dates etc. I
want to refresh the blank cells that once had a formula, so it will not be
included in COUNT.

Thanks

 

[Dave Peterson]

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

===
(and they won't be included in the =CountA() totals. (They wouldn't have been
included in the =count() totals.)