Macro to get target value, clear range and put back target value

**brunces** · 08-24-2017, 09:07 AM

Hey, guys.

I need to enter something in a cell within a range, but, after entering the data, I need Excel to clear all the other cells of the range, because there must be only one cell with data within that range.

So far, I managed to get it working, but if I use the Application.ScreenUpdating option, it gets into an infinite looping and crashes.

I've attached a sample so you can see what I really intend to do.

If there's anything to be corrected or rewritten, please be my guest and do whatever it takes to get the code smoother and correct.

Thank you guys very much for your attention.

brunces

**CK76** · 08-24-2017, 09:23 AM

FYI - Since you are making change to worksheet in the code itself, it will trigger another Worksheet_Change Event and falls into infinite loop.

You should, set Application.EnableEvents to false at start of your code and turn it back on before exiting the sub.
While you have it in your code, it's position is after you make change to cell (i.e. .ClearContents)
So... code should be...

Please Login or Register  to view this content.

**brunces** · 08-25-2017, 07:38 AM

CK76, thank you very much for your help and specially for your explanation. I did not know about that. Now it makes perfect sense to me.

Cheers.

Macro to get target value, clear range and put back target value

LinkBack

Thread Tools

Rate This Thread

Display

Macro to get target value, clear range and put back target value

Re: Macro to get target value, clear range and put back target value

Re: Macro to get target value, clear range and put back target value

Thread Information

Users Browsing this Thread

Similar Threads

Self Ajusting cell , moving to provide target to meet end of year target

Worksheet_Change(ByVal Target As Range) not executing Target inc by 1 as spinner button

[SOLVED] Change(ByVal Target As Range) does not work when Target value changes

[SOLVED] Worksheet_Change(ByVal Target As Range) - set to only update when 3 target cells changed?

[SOLVED] Need alternate syntax 'Select Case' on Target.Column and Target.Row at the same time.

Worksheet_CHange (ByVal Target as Range) when target is formula cell

Ranges:Target in Worksheet_SelectionChange(ByVal Target As Range)

Bookmarks

Bookmarks

Posting Permissions