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.
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.
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”
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”
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
- Range(“H:H,J:J”).ColumnWidth = 10
Autofit can also be done by using Cells.Columns.AutoFit.
Unlock Excel VBA & Macros Course is here.
Save time. Achieve more.
Over 50 Excel macro examples for download & useful VBA codes you can use for your work.
Learn the WHY not just the HOW