VBA Alternative for xlPasteValues?

**paulbbrown0** · 12-15-2010, 04:49 PM

I have some very large spreadsheets with many Boolean formulas (nested IFs, cell value comparisons, etc.). I am looking for an alternative to the methodology of Copying a range (i.e "Selection.Copy") followed by using the PasteSpecial function (i.e. "Selection.PasteSpecial Paste:=xlPasteValues"). This methodology works, but it is taking hours to to process, even when I turn off screen updating and automatic calculation.

I have been able to use an extremely efficient technique of equating the elements of an array to the values in a range (i.e. " Array = Range(x, y).Value") then equating the values in a target range back to the elements of the array (i.e. " Range(X, Y).Value = Array").

This works extremely fast in resolving all of the formulas when the cell formulas are merely mathematical equations that reference values in other cells. However, when the cell formulas include Boolean logic and comparisons, this VBA technique results in merely keeping or moving the original formulas from the source range to the target range.

Ironically, the cell displays on the spreadsheet grid already have all of the formulas resolved. Is there a VBA method to efficiently capture the display values rather than the Formula Window values from a range?

Note: Environment is Windows XP and Microsoft Office 2003.

Thank you for your help.

**romperstomper** · 12-15-2010, 06:56 PM

Assigning the Value property of a range to a variable should always give you the values and not the formulas, regardless of what type of comparisons are involved. Can you post a sample workbook demonstrating that behaviour?

**paulbbrown0** · 12-17-2010, 01:08 PM

Thank you, RomperStomper. Based on your reply I reviewed my testing more carefully and found that the values from the formulas were being pasted back to the original cells while the target cells were being ignored. I am still doing more testing before replying with changed code and the results I am getting.

**JBeaucaire** · 12-15-2010, 08:29 PM

My guess is your "time" issue is still being affected by your use of "select" then "selection.copy" then "selection.pastespecial".

All the "selecting" is physically affecting the sheet, for no gain. Don't do it. Remove all those selection references and issue your commands directly to the range and your macro will increase in speed quite noticeably.

Range("A1:A20").Copy 
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues

**paulbbrown0** · 12-17-2010, 01:11 PM

Thanks for your input. I am sure you are right. I am still working on code that will move the values from the source workbook to a different target workbook without changing the formulas in the source workbook.

**paulbbrown0** · 12-17-2010, 05:16 PM

I want to thank romperstomper and JBeaucaire for their contributions and help.

I am attaching a simple spreadsheet with a solution macro included.

I also learned that the issue of slow execution was due to multiple factors. I will explain so if you are interested in faster coding this may save you a lot of frustration.

Environment:
Dell laptop, Intel T2400 dual processor @ 1.83 Ghz, 0.99GB of Ram, Windows XP, MS Office 2003.

The Goal:
The Workbook that was giving the most trouble was a mini-monster of 233 MB.
It included 3 sheets of raw data (approximately 1000 rows by 250 columns each). It also included several worksheets FULL of cell formulas ( 1010 rows by 251 columns per sheet). This was one of several workbooks in a series that I knew had to be condensed in order to be manageable as there are updates to the raw data every week.

The Approach:
My approach was to update the raw data, then resolve the formulas and save the results to another workbook name. Then I could read the results from this condensed workbook in order to update the next workbook in the series. (When the formulas are resolved into mere values, the workbook only measures 38 MB in size.)

The Problem:
As indicated in the original Posting, the VBA code was taking many hours to process using the Select, Copy, and PasteSpecial technique. Previously on this forum I had learned a number of techniques for speeding up the code. In one instance copying an entire sheet of data (1000 X 250) took only seconds rather than half an hour.

However, when copying values from complex formulas, I still experienced significant time issues when large quantities of cells were involved. I built some time stamps into my code, and experimented with the order in which I converted the various sheets of formulas. In doing so I learned some major lessons. One large sheet of formulas was taking 7 hours to process using the Select, Copy, PasteSpecial method, only took 4 hours when the values were read into an array then read back into the sheet. Furthermore, while it took 4 hours to process when it was the first sheet of formulas to be resolved, it only took one minute when it was the last worksheet to be resolved. Further experiments demonstrated that when the size of the whole workbook had been reduced below 200MB worksheet conversions that had been taking one to four hours were all completing in a minute or less for each sheet.

Conclusion:
Reading (using an "=" statement) a large range into an array then writing the values back into a sheet (essentially reversing the same statement) works significantly faster than looping or even Range based copy and paste instructions. Also, in extremely large workbooks, it is far faster to process through multiple workbooks of under 200 MB than using one large one. ( I currently have five workbooks in a series. The problem workbook starts at 233 MB and takes almost 3 hours to process 500,000 cells followed by 5 minutes to process the remaining 750,000 cells. The remaining workbooks vary in size from 50 MB to 193 MB and only take 15 minutes to process their 4,000,000 cells.)

My next challenge is to restructure the largest workbook so I can break it into at least two smaller workbooks.

Note:
The solution macro included in the attached workbook has a number of code accelerating techniques included.

**broro183** · 12-17-2010, 10:08 PM

hi,

WOW!
They sound like big spreadsheets - perhaps some of the people on here could help you optimise your formulae too. Have you looked at Charles Williams' http://www.decisionmodels.com/optspeed.htm site?

Your use of ".activate" is likely to have a significant impact on your file speed too. Here's an example that explicitly references the ranges right up to the workbook level, thus removing the need for ".activate". I haven't tested it on any files of the size you've mentioned, but the concept may still work...

Sub PerhapsAFasterApproach()
'the rest of your declarations...
'dim...
Dim SrceRng As Range
Dim DestRng As Range

'initial code...

'identify the ranges
With Workbooks(SourceWorkbooks).Worksheets(SourceTab)
    Set SrceRng = .Range(.Cells(SrceRowNumB, SrceColNumB), .Cells(SrceRowNumE, SrceColNumE))
End With
With Workbooks(TargetWorkbooks).Worksheets("Target")
    Set DestRng = .Range(.Cells(TgtRowNumB, TgtColNumB), .Cells(TgtRowNumE, TgtColNumE))
End With

'transfer the values
DestRng.Value2 = SrceRng.Value2

'free memory
    Set SrceRng = Nothing
    Set DestRng = Nothing
End Sub

hth
Rob

**snb** · 12-18-2010, 08:51 AM

You won't even need an array, let alone any loop:

Sub snb()
  with sheets("source").usedrange
    With sheets("Target").cells(1).resize(.rows.count,columns.count).value=.Value
  end with
End Sub

Arrays > 65536 rows will slow down execution.
The fewer loops the faster the execution of your code.

VBA Alternative for xlPasteValues?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Alternative for xlPasteValues?

Re: VBA Alternative for xlPasteValues?

Re: VBA Alternative for xlPasteValues?

Re: VBA Alternative for xlPasteValues?

Re: VBA Alternative for xlPasteValues?

Re: VBA Alternative for xlPasteValues?

Re: VBA Alternative for xlPasteValues?

Re: VBA Alternative for xlPasteValues?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions