Solved Use VBA to stop formulas from being deleted

June 25, 2017 at 09:08:52
Specs: Macintosh
BRENDAN June 24, 2017 at 19:04:45
I have this macro to clear data from cells so the sheet can be reused these are all unprotected with no formulas in them Worksheets("good one protected").Unprotect Password:=
Range("C5:O11").Select
Selection.ClearContents
Range("F16:H20").Select
Selection.ClearContents
Range("K18:L20").Select
Selection.ClearContents
Range("N18:N22").Select
Selection.ClearContents
Range("P18:Q22").Select
Selection.ClearContents
Range("K22:L22").Select
Selection.ClearContents
Range("c1").Select
Selection.ClearContents
but i have 3 ranges k18 to k22 n18 to n22 p18 top22
that have formulas in them can you tell me how to delete the data but keep the formulas
using via in this formula
any help would be great thanks Brendan

Mac air Excel 16

message edited by BRENDAN


See More: Use VBA to stop formulas from being deleted

Reply ↓  Report •

#1
June 25, 2017 at 09:47:41
Try this and see how it works for you:

Press the F5 key, which will bring up the GO TO window
Press the Special button at the bottom of window, which will bring up the GO TO SPECIAL window
Select the Constant button
Click OK
Click on the DELETE Key

All your data will be gone, but formulas will remain.

If necessary, you can use the Macro Recorder to create a macro of the above.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
June 25, 2017 at 13:57:50
It appears that you did not honor this request:

Finally, please click on the following link and read the instructions on how to post Data and Macros in this forum so that they are easier for us to read. You should use the pre tags when you post your VBA code in the new thread.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#3
June 25, 2017 at 15:22:46
✔ Best Answer
First, allow me to comment on your macro, then I will address your question.

When you record a macro, you typically get something close to what you want, but you end up with code that is very inefficient and cumbersome to read. While recording a macro is a fine first step and a great time saver, you should then go into the VBA editor and "clean up" the code. It will not only make the code easier to read for everyone (including you) it's a great way to learn VBA by seeing what changes work and what doesn't.

For example, rarely do you need to Select an object within the macro in order to perform an operation on it.

These 2 instructions...

Range("C5:O11").Select
Selection.ClearContents

...can be combined into one line of code:

Range("C5:O11").ClearContents

That is more efficient because Excel doesn't have to Select each range before clearing the contents. VBA does all the work without forcing Excel to Select the cell first.

In fact, that entire section can be reduced to a single line of code:

Range("C5:O11,F16:H20,K18:L20,N18:N22,P18:Q22,K22:L22,C1").ClearContents

In fact (again) if you give that range a Name within the workbook, e.g. myRange, that long instruction can be reduced to:

Range("myRange").ClearContents

OK, as for the question about not clearing the formulas, I first have to ask this:

What are you including the formula cells in your macro if you don't want them cleared?

Now, I will take a guess and assume that the formulas return invalid results when the rest of the cells are cleared. If that's the case, then in reality you don't want clear the formula cells, you just don't want them to display a result.

If that's the case, then my next question is this:

Is there a cell in the non-formula ranges that those formulas are dependent on? In other words, a cell that must contain data for the formula to return a valid result? If so, change your formulas so that they display a blank cell until that cell (or cells) is filled in. Then eliminate the formula cell from your macro so that it is not cleared.

In other words, let's say that the formula in K18 requires a value in C1. Wrap that formula in an IF so that K18 displays a blank cell when C1 is empty. Put this in K18:

=IF(C1="","",Your_Current_K18_Formula)

As soon as the macro clears C1, K18 will display an empty cell because the IF will be TRUE.

If the formula needs more than piece of data to return a valid result, include all the cells in the IF:

=IF(OR(C1="",G17="",K19=""),"",Your_Current_K18_Formula)

Once the macro clears those cells, K18 will be blank. It will stay blank until all of those cells are filled in.

These are just ideas/techniques. Obviously, without knowing how your spreadsheet is laid out or used, I don't know if those suggestions will work. You will have to us why they don't.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
June 25, 2017 at 23:24:35
Thank you for the response ,the selecting constants worked great.

As for putting my info in there is only 1,2,3. On ask question page
I have no idea where the pre tags selection is ,
its supposed to be above the reply box but there is no reply box on this page
It is very confusing.
I just want to ask a question what are tags ,where are they ,and what do they do.

Anyway I appreciated all the help I've received on this site it's been a great help
Much appreciated Thank you again.
Brendan


Reply ↓  Report •

#5
June 26, 2017 at 03:47:32
The pre tag icon is above Box 2 on the Ask A Question page, similar to where it is above the Reply box. All of the formatting icons (bold, italic, pre, etc.) are available on both pages.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Ask Question