Computing Staff
  • 0

Use VBA To Stop Formulas From Being Deleted

  • 0

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

Share

1 Answer

  1. 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.

    • 0