Remove formulas from Excel sheet

Question

I'm working on one project where I'm using VBA and inbuilt formulas in Excel Macro file. In the end want to remove formulas once it has done processing and calculating.

Option 1:- sht.UsedRange.Value2 = sht.UsedRange.Value2
Option 2:- rng.Value2 = rng.Value2 ' for specific range

Issue
Trying to remove Excel formulas using above code. However, values are pasted in other cells i.e. earlier structure is getting changed to other addresses. e.g. A5 value is placed in A10.
So not sure what's happening here. Any suggestion?

So I have used cell to cell logic to replace my formulas. cl.Value2 = cl.Value2

This is working perfectly but very time consuming. Any suggestions?

`rng.UsedRange.Value2 = rng.UsedRange.Value2` Should be `rng.Value2 = rng.Value2` — Siddharth Rout, May 28 '21 at 05:57
@SiddharthRout, yes, thats what i meant. ```rng.Value2 = rng.Value2```. Question, corrected now — 1S1a4m9, May 28 '21 at 09:20
Both Option1 and 2 are correct. Can you share the complete code? I mean how are you declaring and setting your variables and finally using the above options? — Siddharth Rout, May 28 '21 at 09:22

score -1 · Answer 1 · answered May 28 '21 at 06:04

-1

after copying the value from one cell to another or range to another range , delete or paste special (if you need values) the first cell or range ie. copy from a5 to a10 then delete or paste special A5

i hope it will work for you

answered May 28 '21 at 06:04

Durga Prasad

9
5

In this case a user select entire sheet then copy, right click and paste values. No need to paste to another location. – Harun24hr May 28 '21 at 06:34

Remove formulas from Excel sheet

1 Answers1