We help IT Professionals succeed at work.

Reset a Range

255 Views
Last Modified: 2013-12-30
Hi All,

I am trying to do a "reset" every time I open a workbook.  Specifically I would like the worksheet ("BegDay") to do the following in the range J10:J1000

'Place the number 1,000,000 in each and every cell of the range of J10:J1000
'Then immediately place "" in each and every cell of the range of J10:J1000


This should happen every single time the work book opens.

Any ideas?

thanks,
Comment
Watch Question

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I'm a little confused by why you first want to set all the cells in the specified range to 1,000,000 and then to "".

Nevertheless, the following macro does that. Note that statements 3 and 4 in the snippet below are two alternative ways of clearing the values in J10J1000. The first one puts two double quotes in those cells. The second removes all values.

Put the following code in ThisWorkbook code pane. It won't work at all if installed anywhere else.
Private Sub Workbook_Open()
Worksheets("BegDay").Range("J10:J1000").Value = 1000000
Worksheets("BegDay").Range("J10:J1000").Value = """"        'What you asked for
'Worksheets("BegDay").Range("J10:J1000").ClearContents       'What I think you need instead
End Sub

Open in new window

Author

Commented:
Thanks for that byundt.

Not quite what I need.  I am doing a "persistent max and min" function and need this to be a tweaked a bit more.

I put your code above in it didn't work.

I think what I need is for:

Worksheets("BegDay").Range("J10:J1000").Value = 1000000

....to fill down the range first....and then for....

Worksheets("BegDay").Range("J10:J1000").ClearContents

....to fill down the range after each cell has a 1000000 in it.  Maybe this can be accomplished by a 15 second delay?  

thoughts?

thanks,
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Precisely Perfect!!!  Thanks so much and Happy New Year!!!

Author

Commented:
Just Awesome!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.