Excel VBA

Referring to Ranges & Writing to Cells in Excel VBA (Range, Cells, Offset, Names)

It’s important to be aware of the different ways you can write to Excel cells with VBA.

The macro recorder has its own preference when writing to ranges but it’s not the only way.

You’ll be surprised at all the available options.

Each method has its own advantages.

They will become more obvious later on when you learn to loop inside a range in Excel.

You’ll learn how to use ranges, cells property, offset, named ranges, difference between activecell and selection, and how to reference entire rows or entire columns and also how to use autofit in Excel VBA.

Practice along with me by opening up a blank Excel spreadsheet.

Make sure you close other spreadsheets as we’ll be writing to the active Workbook.

Setting up the module

Open the Visual Basic Editor (VBE) by using the shortcut key ALT + F11.

Right click in the Project Explorer window and select Insert Module.

The module can be renamed in the Properties Window.

In this example, the name LessonsRanges is used.

Writing the Sub procedures

Options Explicit is important when the Sub procedures work with variables.

Create a new Sub procedure by starting with the keyword Sub.

Pressing ENTER will automatically add the End Sub statement.

The methods for a particular Sub procedure should be written in the space between these.

The Immediate Window is useful when doing tests.

To activate this, click View > Immediate Window or by using the shortcut key CTRL + G on the VBE.

Note that when the worksheet name is not indicated in the code, it automatically executes the statements in the active sheet.

Active cell vs Selection

ActiveCell is the cell where the cursor is.

Selection refers to the cell or range that is highlighted.

This can be tested out by highlighting a range on the Sheet and writing the following statements in the Immediate Window:

?ActiveCell.Address

?Selection.Address

Referencing a cell/range and changing the value

Assigning a value of a cell or range can be done using statements in the sub procedure.

There are various ways to refer to cells and ranges using a combination of punctuation marks.

The general syntax of referring to cells and ranges is Range(Cell1, [Cell2]).

Values are then assigned by using the Value property (.Value) or by using the = symbol.

NOTE: Adding Cells.Clear at the start of the subprocedure ensures that all cells are emptied before the methods are executed.

Below are the different ways to refer to ranges.

To test the sub procedures, press the F5 button.

Single cell
- Range(“A1”).Value = ”1st”
Single range using a colon
- Range(“A2:C2”).Value = “2nd”
Multiple ranges separated by a comma
- Range(“A3:C3,E3:F3”).Value = “3rd”
Multiple cells separated by a quotation marks and a comma
- Range(“A4,C4”).Value = “4th”
Single range by specifying the start cell and end cell
- Range(“A4”,”C4”) = “5th”
Single range by concatenating the column letter and row number. This is useful when using a loop with a variable in place of the row number.
- Range(“A” & 6,”C” & 6) = “6th”
Using the Cells property of the Range object by specifying the row number and column number. This is especially useful when looping through many columns and different rows.
- Range(Cells(6,1), Cells(6,3)).Value = “6th”
Highlighting a range and referencing a specific cell within that range.
- Range(“A4:C7”).Cells(4,2)).Value = “7th”

OFFSET()

This function allows you to change a value of a cell by specifying a starting point and the number of rows and columns to offset from it. This is done using the Offset property of the range.

Syntax is as follows: Offset(number of rows, number of columns)

Offset a cell
- Range(“A1”).Offset(7,2)).Value = “8th”
Offset a range
- Range(“A1”).Offset(7,2)).Range(“A1:A4”).Value = “8th”

Range(“A1:B1”).Offset(8,1)).Value = “9th”

Using the name manager

Rename the cell by selecting a cell and going to the name box.

After which, a value can be assigned to this specific cell:

Range(“LastOne”). Value = “10th”

Going through each line of code (Debug)

To do this, click anywhere in the Code Window and press F8. It will then highlight a single row and executes it as you scroll past it.

To resume and run through the rest of the code, press F5 or play.

Another way to do this is to go to Debug > Step Intro.

Referencing entire rows and columns

This is similar to referencing a range.

In the examples below, the RowHeight and ColumnWidth properties will be adjusted.

Refer to rows and specify a row height
- Rows(“12:14”).RowHeight = 30

Refer to separate rows
- Rows(“16:16,18:18,20:20”).RowHeight = 30

Refer to columns
- Columns(“E:F”).ColumnWidth = 10
Refer to separate columns
- Range(“H:H,J:J”).ColumnWidth = 10
  - This adjusts the width of columns H and J, skipping column I.
- Range(Columns(1),Columns(3)).ColumnWidth = 5
  - This adjusts the first column to the third column