Clearing or Deleting cells with formulas returning ""

I need a macro line that will delete the contents of cells in a range
that have formulas that return a blank cell. the if formulas in the
cells return "" or some number. i need to empty the cells that do not
return a number.

Fred R.
scaryboy (10)
3/11/2006 9:55:12 AM
excel 39879 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 50

Select your range to fix
Select Formulas
But uncheck Numbers
Leave Text, Logical, Errors checked
Click Ok

Now hit the delete key.

If you record a macro when you do it, you'll have your code.

scaryboy wrote:
> I need a macro line that will delete the contents of cells in a range
> that have formulas that return a blank cell. the if formulas in the
> cells return "" or some number. i need to empty the cells that do not
> return a number.
> Fred R.


Dave Peterson
petersod (12005)
3/11/2006 12:05:17 PM
Dave Peterson,

That worked perfectly. Thanks a ton

Fred R.

On Sat, 11 Mar 2006 06:05:17 -0600, Dave Peterson
<> wrote:

>Select your range to fix
>Select Formulas
>But uncheck Numbers
>Leave Text, Logical, Errors checked
>Click Ok
>Now hit the delete key.
>If you record a macro when you do it, you'll have your code.
>scaryboy wrote:
>> I need a macro line that will delete the contents of cells in a range
>> that have formulas that return a blank cell. the if formulas in the
>> cells return "" or some number. i need to empty the cells that do not
>> return a number.
>> TIA
>> Fred R.

scaryboy (10)
3/11/2006 5:00:00 PM

Similar Artilces:

From VBA, how do I test for a cell with value #N/A
For example, Cells(1,8) contains #N/A. The following VBA statement: If Worksheets("Sheet1").Cells(1, 8).Value = "" Then gives a type mismatch error. Thanks for any suggestions. You could use the .text property: If Worksheets("Sheet1").Cells(1, 8).Text = "" Then Or you could use: if iserror(worksheets("sheet1").cells(1,8).value) then 'do something with the error else if worksheets("sheet1").cells(1,8).Value = "" .... You might even be able to use: if isempty(worksheets("sheet1&...

Email messages are being deleted
Hi guys Not entirely sure if this is the right group but anyway here goes. Somehow I've altered something which has made my Vista Windows Mail 6.0.6000.16386 delete each mail message once I've read it. The deleted messages don't even go into the deleted items folder. Everything was working fine until I started messing with the settings to try and delete old usenet postings. I've tried to back out the changes which I remember making, but so far no success. Thanks in advance -- David They're not deleted. While in the Inbox: View | Current View...

If formula #3
In a formula how do i say, larger than 56 but smaller than 420... o larger than 56 AND smaller than 420 -- Message posted from Hi something like =IF(AND(A1>56,A1<420),"Yes","No") -- Regards Frank Kabel Frankfurt, Germany > In a formula how do i say, larger than 56 but smaller than 420... or > larger than 56 AND smaller than 420? > > > --- > Message posted from ...

Users can't empty deleted folder from webmail
We're using E2K. A handful of people only get to their email thru webaccess. There are a few of these individuals that cannot delete their deleted items folder while in webmail. What they have to do is click on the deleted items folder, then click on each individual email and delete them that way. But if they try by clicking on the empty deleted items folder icon, they get the following msg, " An error occurred while trying to empty the deleted Item folder". Please advise. Jim ...

Returning to previous record on Scrolling form
I have a very long form with sometimes hundreds of records. For each record, there is a text box where the user can enter a quantity to be added/deleted to the existing quantity. Once the quantity has been added/deleted, the form requeries so that the quantity displayed is now accurate. Since the form is so long, I want a way for the form to return to the record that the user just updated. In Access 2003, I used this code: dim rst as recordset dim IntKeyVal as string IntKeyVal = idx_IDNumber Application.Echo False <<<other VBA code here to add/delete from existing quantity&g...

Delete Pivot Table Formula
How do i delete calculated fields from pivot table field lists? Thanks, sduffield Please check your previous post of this question... -- HTH... Jim Thomlinson "sduffield" wrote: > How do i delete calculated fields from pivot table field lists? > > Thanks, > sduffield ...

Deleted e-mails and size of PRIV.EDB
We have asked many users to delete old e-mails and we have move numerous users over to our old Exchange 5.5 server to our new Exchange 2003 server. When we look at the size of the PRIV.EDB the file it has not decreased. The server has been rebooted and we still do not see any change. I know that the size of the file should decrease by at least 500MB - 750MB. Does any know how to get the .EDB to release the space? Thanks in advance!!! you must perform an offline defrag of the database to reclaim the space on disk... see this:;en...

Add buttons for Center Across and Paste Formula
It would be very helpful if the Customize Toolbars funtion gave access to buttons for Center Across and Paste Formulas so you don't have to a) traverse through menus or drop-downs, or b) create macros and custom buttons to accomplish these oft-used features. I get tired of always having to create macros on each system I work on in order to set up custom buttons that accomplish these simple tasks. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button i...

conditional formula needed
it might be possible to do this as a conditional formula (=if) otherwise a macro might be needed. the thing i'm unsure about is this formula looks at the cells beneath it and keeps looking until a result is returned, i'm not sure if that can be done as a conditional formula. if you really want to know what this is for send me a pm and i'll tell you. if m3 = 0 then "" if l3 = i then "" if m3 = 1 and if l3 = "c" and if i3 > 0 and if e4 – d3 < -.0066 then result = 1 write result in n3 else go to row 5 if e5 – d3 < -.006...

Cells will not calculate
I am using the replace feature to modify a rather long formula. I have to modify it in each column, abour 30 cells per column. Anyway I am replacing $D with $E = the replace feature works perfectly but after the change the cell does not calculate and return the data... HELP. It is far to tedious to make the chabge manually. =IF(ISNA(MATCH("N12",Schedule!$D$49:$D$102,0)),"",INDEX(Schedule!$B$49:$B$102,MATCH("N12",Schedule!$D$49:$D$102,FALSE))) Even with calculation set to manual, the cell should recalculate after making a find and replace change. Your form...

Why do cells in Excel print shorter than displayed on-screen?
When I type a lot of data in a given cell (textwrapping is on), the entire contents of the cell display correctly. But when I print the worksheet, the cell height appears shorter -- truncating my text. What's up with that? And how do I fix it without adjusting every cell manually (which screws up my display)? ...

Why does office turn off when I delete a message?
I have Office Outlook 2003 SP1 and all critical updates. When I do anything with a message, other than read it (for instance, delete it), Outlook shuts down and the message appears telling me that the program has run into a problem and needs to close. There is no error number and no recourse but to click to either send an error report or don't to MS. Has anyone had this problem and has anyone found a cure for it. Thanks for whatever help you can give me. Try both detect and repair and scanpst.exe. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the disc...

same numer/character at beginning of cells in row
I have to make a worksheet of products. I can copy the part numbers but need to have all the part numbers preceded by "2M" no quotation marks. example: starting number looks like this "el000c" need to have it look like this "3Bel000c". The information is being copied from several other sheets via cut and paste. Is it possible to paste the "el000c" number with the 3B already there and staying after pasting several cells at once? Thanks in advance regardless of the answer. Do you want to precede with "2M" or "3B"? Or is i...

Returning the windowheight of a subreport
I need to adjust the height of a text box that is adjacent to a subreport in the detail section of the main report for each instance as the report is generated. What I've tried doing is below: Dim height as integer height = reports.item("subreport").windowheight me.textbox.height = height With that I got a can't find report error. I was then told to change the code to this: Dim height as integer height = Reports("NameOfReport").Item("Subreport").windowheight me.textbox.height = height However with that I received an application defined or object de...

Round formula
How can I round 980 to 1,000? With 980 in A1, you can use this: =3DROUNDUP(A1,-3) Hope this helps. Pete On Apr 1, 3:07=A0pm, Khaled <> wrote: > How can I round 980 to 1,000? It did help, thanks Pete. On Apr 1, 4:23=A0pm, Pete_UK <> wrote: > With 980 in A1, you can use this: > > =3DROUNDUP(A1,-3) > > Hope this helps. > > Pete > > On Apr 1, 3:07=A0pm, Khaled <> wrote: > > > > > How can I round 980 to 1,000?- Hide quoted text - > > -...

formulas for Actual Duration and BCWP
I have yet to be able to figure out the formulas that derive both Actual Duration and BCWP when using actual data in my plans. I understand from where the data is to come, but I have not found a consistent set of formulas I can use to calculate Actual Duration, which is essential to calculating % Complete...nor can I figure out how MSP (standalone 2007) calculates BCWP. I have asked the question on this forum in the past as to how MSP calculates the EV elements but have not gotten an answer I can use to run calculations myself and have them = Actual Duration or BCWP I see in th...

Delete function frozen-Entourage
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: pop My wife deleted a block of e-mails and before it was completed she clicked on a message in the block that she wanted to save. This cause the tool bar containing the Delete button to turn gray and freeze. Now large blocks of her mail in her Inbox and Delete files show as blank space (white screen) when these folders are opened but either clicking on this space or scrolling through it restores the e-mails listed there. At times clicking on a block of e-mails (Wednesday for example) causes the entire block to turn...

Deleting specific records from a column
Hi All I have a little problem. I have two sets of records - let's say for the purpose of this exercise they are titled "a" and "b". The record sets differ but do have many matching records. My objective is to extract all the "b" records that match up with an equivalent record from the "a" column. The final record set will have these "b" records and their matching "a" records deleted from it. Example Column a Column b Final column earth earth sky sky grain fire fire ...

Can't Delete Budget Items
Money 2003 Delux and Business: Earlier in the year I had two EPay transactions set up to move money to outher accounts. One was a weekly item and the other was a monthly item. I stopped both of these series quite a while back by going to the Bills and Deposits list, right clicking on them, selecting delete and deleting all unrecorded occurancnes of the scheduled transactions. This week I was working on my budget for next year. At the bottom is the "Transfer out of Budget Accounts" area. Both of these are still listed as transfers. When I right click on them for the contect men...

Deleting Contacts and Accounts
We had the capability to delete Contacts and Accounts. Since last week we have only been able to de-activate them. The CRM settings are the same for each of the roles. (They have deletion privileges). ...

deleting hotmail from outlook 2002
I currently have an email address that is and it was setup thru hotmail. I want to change everything to Outlook 2002 and not use hotmail. Currently, outlook is my default mail client and somehow, i have a hotmail folder in outlook. This is where all of my incoming mail goes (inbox folder under hotmail). Also, I have tried to export address book from hotmail and import to outlook but have been unsuccessful. I would appreciate any help!! If your only mail account is Hotmail, you will need to continue to use Hotmail? What replacement mail service do you have in mind? ...

Novice at formulas needs help
Please remember...I'm a novice at EXCEL...self-taught...I want to write a formula that will convert what is in one column to another column based on info in yet another column.... If D=1 and K=B then L=NH...another example... If D=7 through 12(as in grade in school) and K=D then L=IH --- Message posted from Hi use the IF formula. E.g. if your target cell is C1 enter the follwoing =IF(A1="D",B1,"no value") this will enter the value of B1 in C1 if A1 is "D" have a look at the Excel helpfile for the IF formula Frank =IF(AND(D1...

Selecting Font Colors
Greetings group, my 1st post here. I've been trying to figure out how to use/create additional text font colors beyond the 40 palate or 16 additional in the Format Cells dialog. Is this possible? Is there a show codes area to tweak? or better still a color wheel for the Text Font Color available. I'm using: Microsoft(R) Office Excel 2003 (11.8012.6568) SP2 - thanks in advance M.D. About all you can do is modify the existing colors under Tools>Options>Colors>Modify. This is not a global change, just a workbook by workbook basis. You can import your modified scheme...

Formula for calculating # of days between dates minus a particular date if present
I'm trying to create a formula that will calculate the number of days between two dates automatically subtracting a day(s) from the equation if a specified date(s) falls in between the two dates. For example, I don't want 1/15/04 to register as a counted day. If I enter the dates 1/1/04 in one cell and 1/12/04 in another cell then it's a straight foward DATEDIF equation. But if I enter 1/1/04 and 1/28/04, then I want the formula to figure out the number of days between 1/1 and 1/28 automatically subtracting 1/15 from the equation. This gets trickier as I need some calucations th...

range specified by the value in another cell
I have the following problem: I have let say 100 numbers in column A, and a number n<100 in cell B1. I want to have the sum of the first n numbers in column A, so I would need to specifiy the range A1:An, but I need this independently of the actual value of n. I have tried A1:A(B1) with but it doesn't work. So has somebody any idea -- Message posted from Hi 2 ways for it 1. Use the formula =SUM(INDIRECT("A1:A"&B1)) 2. Create a named range RangeToSum=OFFSET(SheetName$A$1,,,SheetName!$B$1,1) The formula will be =SUM(RangeToSum) -- (When s...