Refer to cells by using the shortcut notation in a macro
You can refer to cells by using shortcut notation like the A1 reference style or a named range within brackets […] as a shortcut for the Range property. You don’t have to type the word “Range” or use quotation marks, as shown in the following examples.
[MyRange].Value = “B”
In the following Excel training video, two macros demonstrate how to clear the contents of an Excel range and how to fill data in Excel cells in a named range.
Implementing the macros:
- Click on Tools in the menu bar, select Macro and then select Visual Basic Editor from the options that pop-up
- In the VBA editor click on insert and select Module
- In the workspace type the above two macros and run them one by one by clicking in their respective areas and the then clicking on the ‘>’ run button or pressing F5 from the keyboard
- In the first instance the data from the range [B4:B7] is cleared
- In the second macro the defined named range MyRange (b4:B7) gets filled with B
- You’ll notice that this method helps to avoid typing errors especially if you write many lines of code
Watch the video:
VBA Excel — Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation