DELETE FORMULES
DELETE FORMULES
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: DELETE FORMULES

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    How can I whrite a macro that delete all the formules in column B (+/- 7000 lines)without deleted the values in the column?
    Many thanks.

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 08:45, verluc wrote:
    How can I whrite a macro that delete all the formules in column B (+/- 7000 lines)without deleted the values in the column?
    Many thanks.
    Why not copy and Paste|Special|Values instead?

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following code will overwrite the formula with the values

    Range("A1:A7000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    HTH

    Matt

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 08:59, Matt wrote:
    The following code will overwrite the formula with the values

    Range("A1:A7000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    HTH

    Matt
    Matt, you need to change your first line of code to read:
    Range("B1:B7000").Select



    Going with Matt's code, you could try:

    Range("B1:" & Range("B65536").End(xlUp).Address).Copy
    Range("B1:" & Range("B65536").End(xlUp).Address).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False


    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    How can I whrite a macro that delete all the formules in column B (+/- 7000 lines)without deleted the values in the column?
    macro that delete all the formules

    delete

    If thisis the case and you question does exactly as VBA to delete as i have quoted why bother with VBA, try this

    select col B (all of it best)
    Press CTRL+G
    Click special
    Click formula
    All teh formula cells will be highlighted now
    press delete.

    Done, now why VBA that, hardly woth the effort just a quick!

    BTW can also instead of delete :
    right click on and select cell and clear contence, better me thinks.




    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jack:
    Your assumption is that VERLUC's post means that in some cells in column B there are formulas, and in some other cells there are static values -- in that case what you are suggesting will work wonderfully.
    Barrie and Matt have assumed that VERLUC's post implies that all the cells in column B are formulas and now there is need to convert those into static values and get rid of the formulas -- I believe this is what VERLUC's post implies.
    VERUC: please post back to confirm what you really want o accomplish.

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 17:12, Yogi Anand wrote:
    Hi Jack:
    Your assumption is that VERLUC's post means that in some cells in column B there are formulas, and in some other cells there are static values -- in that case what you are suggesting will work wonderfully.
    Barrie and Matt have assumed that VERLUC's post implies that all the cells in column B are formulas and now there is need to convert those into static values and get rid of the formulas -- I believe this is what VERLUC's post implies.
    VERUC: please post back to confirm what you really want o accomplish.

    Your summary of the suggested solutions is not correct.

    Jack's solution clears the contents of the cells containing formulas, leaving blank cells.

    Barrie/Matt's solution converts cells with formulas to values only. (Also, it does not assume all cells contain formulas.)

    The VBA code to do it could be reduced to :-

    Columns(2) = Columns(2).Value

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 18:23, Anonymuus wrote:
    On 2002-03-25 17:12, Yogi Anand wrote:
    Hi Jack:
    Your assumption is that VERLUC's post means that in some cells in column B there are formulas, and in some other cells there are static values -- in that case what you are suggesting will work wonderfully.
    Barrie and Matt have assumed that VERLUC's post implies that all the cells in column B are formulas and now there is need to convert those into static values and get rid of the formulas -- I believe this is what VERLUC's post implies.
    VERUC: please post back to confirm what you really want o accomplish.

    Your summary of the suggested solutions is not correct.

    Jack's solution clears the contents of the cells containing formulas, leaving blank cells.

    Barrie/Matt's solution converts cells with formulas to values only. (Also, it does not assume all cells contain formulas.)

    The VBA code to do it could be reduced to :-

    Columns(2) = Columns(2).Value
    Hi Anonymous:
    You are right!
    The code works great too!
    T H A N K S !

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 09:10, Barrie Davidson wrote:
    On 2002-03-25 08:59, Matt wrote:
    The following code will overwrite the formula with the values

    Range("A1:A7000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    HTH

    Matt
    Matt, you need to change your first line of code to read:
    Range("B1:B7000").Select



    Going with Matt's code, you could try:

    Range("B1:" & Range("B65536").End(xlUp).Address).Copy
    Range("B1:" & Range("B65536").End(xlUp).Address).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False


    Regards,

    I have create the macro above and it works
    correct.But that was not the total question.
    What's my problem?

    I have on sheet1 all the symbols of the nasdaq stock exhange.Colum A1 to A7000
    In colum B1 to B7000 I set the stock prices who are downloaded in sheet2 with a formule that I have for each line.
    The following day I downloaded the new stock prices in sheet2(overwriting) and I want these new stock prices in colum C1 tot C7000
    So I needed my formules from colum B in C and deleted the formules in B and so on each day so I can follow the stock prices each day.
    Have you therefore a macro to do that automaticly?
    Many thanks.

  10. #10
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I'm not totally sure of your requirements. If I understand correctly, the following code will take the values in column B and copy them to first blank column from column B (leaving the original formulas in column B).


    Range("B1:" & Range("B65536").End(xlUp).Address).Copy
    Range(Range("IV1").End(xlToLeft).Offset(0, 1) _
    .Address).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False



    Is this what you need?

    _________________

    Barrie Davidson
    My Excel Web Page

    [ This Message was edited by: Barrie Davidson on 2002-03-26 12:31 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com