MS Excel Training 

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.
Sub ClearRange()
Worksheets("Sheet1").[B4:B7].ClearContents
End Sub

Sub SetValue()
[MyRange].Value = "B"
End Sub
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




Further reading
Short cut notation page 30


Refer to cells by using shortcut notation