Refer Cells Ranges Using A1 Notation VBA

How to access Excel worksheet cells using a1 notation

We have learnt how to create a new workbook using VBA. Next we learnt how to open the newly created workbook automatically using the ‘Open’ method and then access a specific worksheet with VBA. Today we learn how to access Excel worksheet cells using VBA so that we can enter data into them. We also perform manipulations like formatting on the entered data.

Now a common task when using a macro in Microsoft Excel is to access Excel worksheet cells using a1 notation and then do something with it, such as enter a formula or change the format.  You can usually do this in one statement that identifies the range and also changes a property or applies a method.

A Range object in Visual Basic can be either a single Excel cell (Eg. A1) or a range of Excel cells (Eg. A1:D5).

The following example shows how to reference cells and ranges using A1 notation in Microsoft Excel.

You can refer to a cell or range of cells in the A1 reference style by using the Range property in Excel. The following subroutine changes the format of Excel cells A1:D5 to bold.

Macro code for reference:

Sub FormatRange()

Range(“A1”).Value = “First Name”

Range(“B1”).Value = “Last Name”

Range(“C1”).Value = “Designation”

Range(“D1”).Value = “Salary”

Range(“E1”).Value = “Perks”

Range(“F1”).Value = “Total Package”

Workbooks(“Book1”).Sheets(“Sheet1”).Range(“A1:F1”).Font.Bold = True

End Sub

Implementing the macro:

  1. Click on Tools in the menu bar and select Macro. From the drop-down options select Visual Basic Editor
  2. In the VBA editor window click on the insert menu in the menu bar and select Module
  3. Type the above code. You can type the following code also and it will work although the above code is comprehensive:

Sub FormatRange()

Range(“A1”).Value = “First Name”

Range(“B1”).Value = “Last Name”

Range(“C1”).Value = “Designation”

Range(“D1”).Value = “Salary”

Range(“E1”).Value = “Perks”

Range(“F1”).Value = “Total Package”

Sheets(“Sheet1”).Range(“A1:F1”).Font.Bold = True

End Sub

Instead of bold you can use underline, italics, etc.

Watch the Excel training video below to see the macro in action:

Further reading:

How to: Refer to Cells and Ranges by Using A1 Notation

One thought on “Refer Cells Ranges Using A1 Notation VBA

  1. Shraddha Parab

    Hello Sir,

    First of all thanks a lot for all the videos they are really helpful.

    I wanted to understand how allocations of tasks can be done on basis of preferences (at least 2 preferences if given) and marks( highest to be considered first)

    Reply

Leave a Reply

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