Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to write a macro that will clear validated cells to blank? | Excel Worksheet Functions | |||
Clear Blank cells | Excel Programming | |||
How to clear 'blank' cells | Excel Discussion (Misc queries) | |||
clear range of cells if another becomes blank | Excel Worksheet Functions | |||
Clear blank cells | Excel Programming |