DELETE FORMULES

verluc

Well-known Member
Joined
Mar 1, 2002
Messages
1,451
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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,
 
Upvote 0
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
 
Upvote 0
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:
<pre>
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
</pre>

Regards,
 
Upvote 0
How can I whrite a macro that delete all the formules in column B (+/- 7000 lines)without deleted the values in the column?
<quote>macro that delete all the formules

<QUOTE> 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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 !
 
Upvote 0
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:
<pre>
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
</pre>

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.
 
Upvote 0
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).<pre>
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</pre>


Is this what you need?

_________________

Barrie Davidson
My Excel Web Page
This message was edited by Barrie Davidson on 2002-03-26 12:31
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top