.values=.values and macro speed

[mbt2103]

Hey all,

I have a question that should be simple, but I couldn't find any information on it in searches, and other people asking similar questions seemed to just generate confusion.

I'm putting together a rather large macro using Excel/VBA 2010, and I was having problems with "empty" cells not actually being empty. I found this nifty little gem (probably on this site):

Range("A:J").Select
With Selection
.Value = .Value
End With

4 lines, fixed all my problems.

However, after implementing it, and while testing further additions to the macro, I discovered that every time it ran, it ran a little slower. One iteration took 15 minutes, when it usually takes <10 seconds. Specifically, making borders becomes very slow. This doesn't end until I reset my computer entirely.

My first thought was to clear the cache. When I searched for methods, most responses were confused "what cache?" and "there's nothing to clear" responses. So now I'm lost.

It seems the values for the entire sheet are being stored somewhere, taking up computing power, and slowing down everything else, and they're not being deleted unless the computer is reset. I'm going to call this chunk of values a "cache".

How do I clear this "cache"?

Thanks for the help! (and, if you're like me, have fun with the mystery!)

 

[JoeMo]

Just a guess, but assuming you are not using anywhere near all the cells in columns A:J, try this:

With Intersect(Columns("A:J"),Activesheet.UsedRange)
.Value = .Value
End With

 

[wigi]

Or:

[COLOR=#333333]With Activesheet.UsedRange.Resize(,10)[/COLOR]
[COLOR=#333333].Value = .Value[/COLOR]
[COLOR=#333333]End With[/COLOR]

 

[mbt2103]

Awesome! Thanks!