When dealing with VBA, as it is basically a programming language, you will hear the terms „hard-coded“ and dynamic. When we discuss these terms in VBA, they often refer to the ranges.
Dynamic ranges are the ones that change as the cells that they consist of are changing. In the example below, we will show how to copy them in Excel.
Copy Static Range in Excel Sheet Using VBA
First, we will show how to copy static range. We will insert the list of NBA players in column A:
To copy and paste this range with VBA, we will first open the VBA by clicking ALT + F11 on our keyboard. On the window that appears, we will select the Insert tab, and then choose Module:
As we only have one sheet in our Workbook, we will insert the following code:
|
1 2 3 4 5 6 7 |
Sub CopyPasteStaticRange() Dim sRange As Range Dim dRange As Range Set sRange = Range("A1:A10") Set dRange = Range("C1") sRange.Copy dRange End Sub |
This code defines two variables: sRange and dRange as a range. Then it sets them to different values: sRange will be equal to the range where NBA players are located (A1:A10), and dRange will be equal to cell C1. We do not have to properly define the end value of this range, as this will be the destination cell for our original range.
For the last part, we copy our sRange and paste it to dRange.
When we execute this code by pressing F5 while in the module, this will be our result:
Copy Dynamic Range in Excel Sheet Using VBA
One huge disadvantage of the code written above is that if we change the scope of our range, i.e. if we add more NBA players in column A, they will not be copied and pasted in column C. We will do just that, and add several players to our table:
If we execute our code again, only the range that we originally defined (A1:A10) will be copied.
To change this, we will tweak our code a little bit:
|
1 2 3 4 5 6 7 |
Sub CopyPasteDynamicRange() Dim sRange As Range Dim dRange As Range Set sRange = Range("A1", Range("A1").End(xlDown)) Set dRange = Range("C1") sRange.Copy dRange End Sub |
The only difference between the first code and the one above is it this line:
|
1 |
Set sRange = Range("A1", Range("A1").End(xlDown)) |
We first set the first cell of our range to be cell A1, and the last one to be equal to the end of that range in the down direction. We use End which has four options (down, left, right, and up).
This makes our code dynamic, as it will always encompass the range that starts with cell A1 and finishes at the end of the range that we have in column A. It is important to note that we will only copy all the cells that are populated. If, for whatever reason, cell A7 was blank and cell A9 was populated, the code would only copy the range A1:A7.
When we execute the code again, our range will be copied and pasted to column C:
