Refer Excel Cells Using Shortcut Notation VBA

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

Watch the video:

Further reading:
VBA Excel — Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation

Leave a Reply

Your email address will not be published. Required fields are marked *