Deleting All Cells with Number formulas Ex. =2+3 not = A1+B2 - 0

Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2

I am trying to create a copy of workbook with only formulas, but some cells 
exist with =2+3 which I do Not wanted. How can I delete all of this in a 
single click 
(Not Individualy) 

0
Samshun (4)
1/22/2005 7:55:04 PM
excel 39879 articles. 2 followers. Follow

4 Replies
579 Views

Similar Articles

[PageSpeed] 33

this should do it

Sub cleanformulas()
 For Each c In Selection
   If c.HasFormula And _
   IsNumeric(Mid(c.Formula, 2, 1)) Then c.Clear
 Next
End Sub


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Samshun" <Samshun@discussions.microsoft.com> wrote in message
news:2270ADD4-A736-4B10-837B-5FDADE3089AD@microsoft.com...
> Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2
>
> I am trying to create a copy of workbook with only formulas, but some
cells
> exist with =2+3 which I do Not wanted. How can I delete all of this in a
> single click
> (Not Individualy)
>


0
Don
1/22/2005 9:16:45 PM
> Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2

This idea looks to see if there are any Precedents within the formula.

Sub Demo()
'// Dana DeLouis
    Dim BigRng As Range
    Dim Rng As Range

    On Error Resume Next
    Set BigRng = Cells.SpecialCells(xlCellTypeFormulas)
    If BigRng Is Nothing Then Exit Sub

    For Each Rng In BigRng.Cells
        If Rng.Precedents.Count > 0 Then
            If Err.Number = 1004 Then 'No cells were found.
                Rng.Clear
                Err.Clear
            End If
        End If
    Next Rng
    ActiveSheet.UsedRange
End Sub

HTH  :>)
-- 
Dana DeLouis
Win XP & Office 2003


"Samshun" <Samshun@discussions.microsoft.com> wrote in message 
news:2270ADD4-A736-4B10-837B-5FDADE3089AD@microsoft.com...
> Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2
>
> I am trying to create a copy of workbook with only formulas, but some 
> cells
> exist with =2+3 which I do Not wanted. How can I delete all of this in a
> single click
> (Not Individualy)
> 


0
delouis (422)
1/22/2005 11:39:33 PM
"Dana DeLouis" <delouis@bellsouth.net> wrote...
>>Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2
>
>This idea looks to see if there are any Precedents within the formula.
>
>Sub Demo()
....
>    If Rng.Precedents.Count > 0 Then
>      If Err.Number = 1004 Then 'No cells were found.
>        Rng.Clear
>        Err.Clear
>      End If
>    End If
....

OP's specs may be incomplete. Should cells containing formulas like =NOW()
or =RAND() be deleted? Your macro deletes them. It also deletes DDE formulas
and cells containing external references into closed workbooks when there
are no references to ranges in the formula's parent workbook.

If only cells containing formulas involving only operations with constant
operands should be deleted, then maybe something like


Sub foo()
    Const RE_DQS As String = """[^""]*(""""[^""]*)*"""
    Const RE_TKN As String = "\b[_A-Za-z]"

    Dim rng As Range, r As Range, ws As Worksheet
    Dim re As Object, rf As String

    Set re = CreateObject("VBScript.RegExp")
    re.Global = True
    re.IgnoreCase = False

    For Each ws In ActiveWorkbook.Worksheets
        On Error GoTo Continue
        Set rng = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        For Each r In rng
            rf = r.Formula
            re.Pattern = RE_DQS
            rf = re.Replace(rf, """""")  'reduce all string constants
            re.Pattern = RE_TKN
            If Not re.Test(rf) Then r.Clear

        Next r

Continue:
        Err.Clear

    Next ws

End Sub


0
hrlngrv (1990)
1/23/2005 3:41:05 AM
> ... =NOW() or =RAND() .....Your macro deletes them.

Thanks Harlan.  I never even thought about formulas with no arguments.  Good 
catch.  I like your RegExp formula.  :>)

I was going to suggest a "RegExp" using .Matches over in the thread 'Find 
nth instance of a character in a string', but I don't think it could have 
beaten your time.

-- 
Dana DeLouis
Win XP & Office 2003


"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:%23k%23fI2PAFHA.3708@TK2MSFTNGP14.phx.gbl...
> "Dana DeLouis" <delouis@bellsouth.net> wrote...
>>>Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2
>>
>>This idea looks to see if there are any Precedents within the formula.
>>
>>Sub Demo()
> ...
>>    If Rng.Precedents.Count > 0 Then
>>      If Err.Number = 1004 Then 'No cells were found.
>>        Rng.Clear
>>        Err.Clear
>>      End If
>>    End If
> ...
>
> OP's specs may be incomplete. Should cells containing formulas like =NOW()
> or =RAND() be deleted? Your macro deletes them. It also deletes DDE 
> formulas
> and cells containing external references into closed workbooks when there
> are no references to ranges in the formula's parent workbook.
>
> If only cells containing formulas involving only operations with constant
> operands should be deleted, then maybe something like
>
>
> Sub foo()
>    Const RE_DQS As String = """[^""]*(""""[^""]*)*"""
>    Const RE_TKN As String = "\b[_A-Za-z]"
>
>    Dim rng As Range, r As Range, ws As Worksheet
>    Dim re As Object, rf As String
>
>    Set re = CreateObject("VBScript.RegExp")
>    re.Global = True
>    re.IgnoreCase = False
>
>    For Each ws In ActiveWorkbook.Worksheets
>        On Error GoTo Continue
>        Set rng = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
>        On Error GoTo 0
>
>        For Each r In rng
>            rf = r.Formula
>            re.Pattern = RE_DQS
>            rf = re.Replace(rf, """""")  'reduce all string constants
>            re.Pattern = RE_TKN
>            If Not re.Test(rf) Then r.Clear
>
>        Next r
>
> Continue:
>        Err.Clear
>
>    Next ws
>
> End Sub
>
> 


0
delouis (422)
1/23/2005 10:04:11 AM
Reply:

Similar Artilces:

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Microsoft CRM 3.0 Installation 11-08-07
Good Day board, Quick and to the point. I'm installing a trial copy of Microsoft CRM 3.0 that I got at a computer conference recently and I've gotten everything to work in the System Requirements dialog except that it's telling me that "The minimum required version is SQL Server 2000 SP4 (8.0.2026)". Strange this is that I do believe that I have SQL Server 2005 installed on the machine. Any idea how to be absolutely sure of what version I have installed? Have you choosed the right name of SQL server during installation set- up?` You have to make it sure if it is ...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Find and Replace Footnote Numbering
I often have documents where I need to put formatting before or after the footnote number for every footnote, often for hundreds of footnotes...anyone know how I can do this in one shot? I know I can specify ^f in find and replace, but only in the find portion...it gives an error if you put it in replace. ...

Count If Formula #4
I need a formula that will figure the following: If column C = 3, count all times < 00:45 If column C = 4, count all times < 00:45 If column C = 3, count all times > 00:46 Thanks. Your description is a bit confused.You have different actions for the same value (C=3). And where are the times, in a different column than C? So, making many assumptions, perhaps =SUMPRODUCT(--(ISNUMBER(MATCH(C1:C200,{3,4},0))),--(D1:D200<TIME(0,45,0)))+S UMPRODUCT(--(C1:C200=5),--(D1:D200<TIME(0,46,0))) -- HTH RP (remove nothere from the email address if mailing direct) "Denise"...

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

Offline Synchronization Problem #2
I get the following error when I try going offline. The excelption is taken from Event Viewer of the Client Machine. Event Type: Error Event Source: MSCRMOfflineSync Event Category: None Event ID: 6000 Date: 3/25/2008 Time: 5:03:33 PM User: N/A Computer: WINXP Description: An error occurred during Offline Synchronization. Try going offline again, or restart Microsoft Outlook. saInsert failed for entity 'ActivityMimeAttachment', batchRows=0 with exception System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument li...

Emails #3
Hi, I was wondering if anyone knew of any web based email provder that I could use that wont be bloked but the I.T Filer at my work. I require use of emails during the day for personal use but work emails are monitored. I have tired various sites i.e Hotmail, Yahoo, Gmail, lycos etc but they are all blocked. Does anyone know of any that may not be picked up buy the web filer. Fiona Fiona, It is difficult to answer because it depends on what your filter is and how it is monitored. For instance, if it is actively monitored and somebody found out you were accessing the site, then it coul...

Unable to delete empty database
I recently moved a group of users out of one database into another Database within the same storage group. After successfully moving all users (except SMTP and SystemMailbox) out of the 1st database I attempted to delete the database only to receive an error: One or more users currently use this mailbox store. These users must be moved to a different mailbox store or be mail disabled before deleting this store. Id no: c1034a7f Exchange System Manager Can I delete this mailbox via ADSIEDIT? Is this the recommended alternative? Thanks, BJ Use the AD Users and Computers snap-in ...

Contacts in Exchange #2
We are running Exchange 2003 and Outlook 2003. I have tried to give a user rights to her boss's contacts through delegation and setting the rights on the contacts folder, however, while she has the rights she is unable to add or see her boss's contact folder in her Outlook. She can see it if she runs a search, but can not permanently put it in her list of contact folders. IS there a way that I can accomplish this through the back-end? Thanks, Joseph rapoport jrapoport@insurmark.net ...

How to delete a set of rows depending on Value
I have two worksheets Worksheet A 27 Columns 1600 Rows. Worksheet B 1 Column 200 Rows I need Worksheet A to look at each cell in Worksheet B, if a cell exists in Worksheet A, then I need the row deleted... Basically I have a list of Grand list of items to do, then a list of items already completed. I need to now remove all entries in the grand list that have been completed. Is this feasible or should I look at using some Unix script. It sounds like you could use VLOOKUP to find out if the value in B exists on A: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel....

learning Excel #3
Hi, I was considering learning Excel as an additional tool for my data analysis work. Is it better to use data sets that I have previously used with SPSS and apply the same analysis tools as in SPSS? For applying the appropriate tools, I was considering using excel's online help. The second option I have is to use some excel book for data analysis and apply the techniques to data sets provided with the book. Any suggestions????? regards Metal ...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Goal Seek #2
Can anyone tell me a little about goal seek? Hi basically it's used to change an input variable so that the result of the formula equals what you want it to ... say i have the following A B C 1 10 20 =A1+B1 now C1 will give me 30, but if i want to know what B1 will need to be if i want C1 to show 50, i can use goal seek Set C1 to value of 50 by changing B1 Hope this helps Cheers JulieD "Jamie Hart" <jhart@emaple.net> wrote in message news:u3OscELdEHA.244@TK2MSFTNGP12.phx.gbl... > Can a...

How do I Remove a Split from my Comments in Excel 2003? #2
I have set my current workbook to split/freeze the first column and first 2 rows. Now, when I add a comment to the second row (in any column) my comments are cut off if I should scroll down. I don't ever remember the behavior before. And I don't know what I've done to enable it but it's really annoying. How do turn this off ? ...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

Formula for Game Spreadsheet
I am currenly playing a game of Ars Magica. I am using a spreadsheet to compute the info for my character. I am having a probllem with the casting total for spells related to a paticular character. The Formula is as follows : =IF(H13="","--",IF(J13="",INDIRECT(H13),MIN(INDIRECT(H13),INDIRECT(LEFT(J13,2)),INDIRECT(RIGHT(J13,2))))+IF(K13="",INDIRECT(I13),MIN(INDIRECT(I13),INDIRECT(LEFT(K13,2)),INDIRECT(RIGHT(K13,2))))+Sta+IF(N13="",0,$H$2)) I am trying to compute the casting total for the spells castable by this character. The total for the ...

Setting a dynamic range in a formula
Hi, I have a column of numbers and I always want the following arra formula to use the last 12 entries: =(PRODUCT(1+D1:D12/100)-1)*100 Any suggestions? Thanks, Phillycheese -- Phillycheese ----------------------------------------------------------------------- Phillycheese5's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2419 View this thread: http://www.excelforum.com/showthread.php?threadid=37809 Assuming that Column D contains no blanks, try... =(PRODUCT(1+OFFSET(D1,MAX(0,COUNTA(D:D)-12),0,12,1)/100)-1)*100 ...confirmed with CONTROL+SHIFT+ENTER. Hope th...

random number generator
Hey is there a way to generate random numbers like you would for a draft. In other words, i have numbers 1 through 10 and generate a random order for those? Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 and copy it down through B10. Recalc (F9), then sort by column B. Hope this helps, Hutch "pat67" wrote: > Hey is there a way to generate random numbers like you would for a > draft. In other words, i have numbers 1 through 10 and generate a > random order for those? > . > You would have to generate 10 random numbers ...

A message that cannot be deleted, opend or moved
THere is a message in a user's inbox that cannot be opened, deleted or moved. How can I delete that message ? Amila Amila Chandrasekera <amilanc@spss.com> wrote: > THere is a message in a user's inbox that cannot be opened, deleted or > moved. How can I delete that message ? One drastic way is to create a new PST file and copy everything from the old file to the new, with the exception of the misbehaving message. Then remove the old file. did you try shift-delete? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote ...

formula or VBA
I need to search through a column checking condtions. If the conditions are met, then I want to enter the corresponding value in the column next to it into a different cell. Basically, I want to search column A and, if the conditions are met, put the value in B into C. Would this be easier via formula or code? And maybe some starting suggestions would be nice if you don't mind. -- Thanks, Jim in a formula in col b =if(cond,a1,"") using vba for each c in range("a1:a100") if cond then c.offset(0,1)=c next -- Don Guillett SalesAid Software donaldb@281.com "...

Increment A2 from A1 and A2 Sum #2
I have two cells: A1 - manual value B1 - automatic increment = B1 + A1 How can i make it? (=error: circular reference) Note: I have this formula repeated in some lines: = B2 + A2 = B3 + A3 .. -- Message posted from http://www.ExcelForum.com ...

number format with leading 0
Dose anyone know how to create a number format that would show a leading zero for the numbers 0-9. That is, so that 1 would show as 01. I can do with a text format but would like to retain the integrity of the number. Thanks Try this: Format>Cells>Number Category: Custom Type: 00 Click [OK] Does that help? *********** Regards, Ron "Darby" wrote: > Dose anyone know how to create a number format that would show a leading zero > for the numbers 0-9. That is, so that 1 would show as 01. I can do with a > text format but would like to retain the integrity...