Reset a Range

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,
BostonBobAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
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

BostonBobAuthor 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,
byundtMechanical EngineerCommented:
OK. Here is code with a 15 second delay:
'Put the following two subs in ThisWorkbook code pane. They won't work at all if installed anywhere else!
Private Sub Workbook_Open()
Worksheets("BegDay").Range("J10:J1000").Value = 1000000
Application.OnTime Now + TimeSerial(0, 0, 15), "ThisWorkbook.ClearCells"
End Sub

Private Sub ClearCells()
Worksheets("BegDay").Range("J10:J1000").ClearContents       'What I think you need instead
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BostonBobAuthor Commented:
Precisely Perfect!!!  Thanks so much and Happy New Year!!!
BostonBobAuthor Commented:
Just Awesome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.