Clear Blank Cells
In B1 I have =if(A1="this","that","") and continuing down b:b
i then copy b:b and paste special (value) into c:c I have a sumproduct equation that runs off c:c. the problem is that apparently the blank cells ("") arent really blank. I get a #VALUE error. How can I find all the blank (''") cells and clearcontents? Thanks in advance jeff |
Clear Blank Cells
What you want to find and clear is "*" , which is wildcard any string.
That would find cells with formulas that equate to zero or "" . "choice" wrote: In B1 I have =if(A1="this","that","") and continuing down b:b i then copy b:b and paste special (value) into c:c I have a sumproduct equation that runs off c:c. the problem is that apparently the blank cells ("") arent really blank. I get a #VALUE error. How can I find all the blank (''") cells and clearcontents? Thanks in advance jeff |
Clear Blank Cells
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 choice wrote: In B1 I have =if(A1="this","that","") and continuing down b:b i then copy b:b and paste special (value) into c:c I have a sumproduct equation that runs off c:c. the problem is that apparently the blank cells ("") arent really blank. I get a #VALUE error. How can I find all the blank (''") cells and clearcontents? Thanks in advance jeff -- Dave Peterson |
Clear Blank Cells
Consider:
Sub clear_blank() For Each r In ActiveSheet.UsedRange If r.HasFormula And r.Value = "" Then r.Clear End If Next End Sub -- Gary''s Student - gsnu200746 "choice" wrote: In B1 I have =if(A1="this","that","") and continuing down b:b i then copy b:b and paste special (value) into c:c I have a sumproduct equation that runs off c:c. the problem is that apparently the blank cells ("") arent really blank. I get a #VALUE error. How can I find all the blank (''") cells and clearcontents? Thanks in advance jeff |
All times are GMT +1. The time now is 11:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com