DELETE FORMULES

[verluc]

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.

[Barrie Davidson]

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,

[Matt]

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

[Barrie Davidson]

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,

[Jack in the UK]

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.

[Yogi Anand]

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.

[Anonymuus]

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

[Yogi Anand]

On 2002-03-25 18:23, Anonymuus wrote:

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 !

[verluc]

On 2002-03-25 09:10, Barrie Davidson wrote:

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.

[Barrie Davidson]

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 ]