Excel VBA

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

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.

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”

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

Autofit can also be done by using Cells.Columns.AutoFit.