Скачать или смотреть How to Avoid Using the Clipboard in Excel VBA for Faster Performance

Discover how to efficiently copy ranges in Excel using VBA without relying on the clipboard, enhancing your workflow and speeding up your tasks.
---
This video is based on the question https://stackoverflow.com/q/70306368/ asked by the user 'steven' ( https://stackoverflow.com/u/16069461/ ) and on the answer https://stackoverflow.com/a/70306410/ provided by the user 'Scott Craner' ( https://stackoverflow.com/u/4851590/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Is it possible to avoid using the clipboard by copying a range?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Avoid Using the Clipboard in Excel VBA for Faster Performance

If you've ever faced slow performance when copying and pasting data in Excel, especially when using VBA, you're not alone. Many users find that their code takes longer to execute than expected. One common reason for this lag is the reliance on the clipboard. This article will explore an efficient approach to copy ranges in Excel using VBA without ever touching the clipboard.

The Problem with the Clipboard

When you use the clipboard in Excel, it not only consumes time due to the temporary storage of data but can also lead to interruptions if the user needs to perform other activities. This can be particularly frustrating in large Excel files where speed is essential.

Original Code Example

Consider the following code snippet that you might be using:

[[See Video to Reveal this Text or Code Snippet]]

While this code accomplishes the task, the use of the clipboard can significantly slow down the process, particularly with larger datasets.

An Efficient Solution: Using the Range Object

Step 1: Create a Range Object

Instead of using the clipboard, you can create a Range object which allows direct manipulation of the data. This can be done with the following code:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

Dim rng As Range: This line declares a new variable called rng that will hold the range of data.

With Sheets("Sheet 1"): This block references "Sheet 1" to minimize the need to repeatedly reference the sheet.

Set rng = .Range(...: This retrieves the desired range efficiently without copying it to the clipboard.

Resize() method: This adjusts the destination range to match the number of rows and columns from the source.

Step 2: Alternative Method with Nested With-Blocks

If you prefer a more compact version, you can utilize nested With blocks for an alternative implementation:

[[See Video to Reveal this Text or Code Snippet]]

Benefits of This Approach:

Clarity: The nested structure can make it clearer which range you are working with.

Performance: Reducing clipboard usage can enhance performance, particularly in large workbooks, as it eliminates unnecessary overhead.

Conclusion

By utilizing a Range object and the Resize() method, you can effectively avoid using the clipboard in your VBA code. This change not only streamlines your code but also improves performance, especially when handling large datasets in Excel.

If you found this approach helpful or have additional tips for speeding up Excel VBA processes, feel free to share your thoughts in the comments below!