asked on

Clearing Cells

EE Professionals,

I have a set of cells that I want to clear or "reset" with a Macro. Currently I was looking at using;
Sub clearstrategicpriorities()
Dim i As Integer
With Worksheets("Strategic_Priorities")
.Range("A4:D43").ClearContents
End With
End Sub

The problem is that the Cells have formulas in them so I don't want to use "clearcontents". What can I use as a command that will allow me to clear the text out of the cells or reset them, without disturbing the formulas?

Thank you,

b.

Neil Russell

The formulas display results. How can you clear the DISPLAYED results but not the formula? I dont quite understand your aim?

If you have a forula in a cell it will display the result

What are you aiming to do?

Bright01

ASKER

OK..... Here's the story; I just looked at what I was trying to do based on your comments. I don't really need to delete the cells that have the formulas in them. I must clear the contents of the cell that forces the text (that the formulas pull in) to clear. So here's the issue, When I use my macro;

Sub clearstrategicpriorities()
Dim i As Integer
With Worksheets("Strategic_Priorities")
.Range("B1").Delete
.Range("A4:A43").ClearContents
.Range("C4:C43").ClearContents
End With
End Sub

The Range(B1).Delete statement causes problems. If I simply go to the cell and backspace the Text out of B1, I have no problems. So I think I need another word other than Delete or Clear Contents.....

Does that make sense?

B.

Neil Russell

Can you upload an example sheet with comments on what you need to achieve?

.Range("B1").ClearContents sounds like ALL you need to me IF THAT is the cell that the others are pulling the text from.

Bright01

ASKER

I was afraid you'd ask that! Yep..... "the beast" is attached. So, save a copy, than do three things;

1.) Use the drop down box to select one of three industries. You will see that the Text changes based on the industry selected.
2.) I have a really cool Macro that puts a drop down box only where/when text is evident (Col. A and C).... you can see it when you change Industries (H,M.L).
3.) If you hit reset, it screws up the entire sheet and you even lose the list box.

What I'm trying to do is to reset the Industry (which removes the text) without losing the ability to "auto-resize" and also remove the sensitive list boxes (Col. A and C) until a new industry is selected.

B.
Clearcontents-macro.xlsm

Neil Russell

Chabge the formula in B4 to be...

=IF(OR(ISBLANK(B1), ISERROR(Priority_Formulas!E4) ),"",INDEX(PriorityDB!$E:$E,MATCH(Strategic_Priorities!$B$1&Priority_Formulas!E4,INDEX(PriorityDB!$A:$A&PriorityDB!$B:$B,0),0)))

And then change your code in module 1 to be......

Sub clearstrategicpriorities()
Dim i As Integer
Application.EnableEvents = False

With Worksheets("Strategic_Priorities")
.Range("B1").ClearContents
.Range("A4:A43").ClearContents
.Range("C4:C43").ClearContents
End With
Application.EnableEvents = True
End Sub

Try that.

ASKER CERTIFIED SOLUTION

Neil Russell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

On a flight from Beijing....will try this on Friday when I land.

Thank you!

Bright01

ASKER

Neil,

Excellent! It worked! I am terribly sorry for not getting back with you sooner. This travel is killing me but hey "the life we choose"! Anyway, very nice work Neil.

Best regards,

B.