VBA Writing To Ranges - Xelplus - Leila Gharani

How to Open VBA in Excel

We will write VBA code by using the Visual Basic Editor (VBE). This is the interface for writing VBA code and creating scripts.

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

The next step is to setup the VBA Module: Right-click in the Project Explorer window and select Insert Module.

The module can be renamed in the Properties Window.

In out 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

Active Cell in Excel VBA refers to the cell that is currently selected or being worked on in an Excel worksheet.

Think of it as the cell that has the spotlight on it at any given moment. When you’re writing macros or scripts in VBA, you can use the active cell to read or change its value, format it, or even navigate to other cells relative to it.

Selection in VBA on the other hand refers to any group of cells that a user has highlighted or selected in a worksheet. This can range from a single cell to a complex range of multiple cells, rows, columns, or even non-adjacent areas.

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

When you’re working with Excel and writing code to automate tasks, sometimes you need to fill in cells with specific information or clean them out before you start. Here’s how to do that in easy steps:

• To change what’s in a cell or a group of cells, we use simple statements in the code.
• You can point to which cell(s) you want to change by using a syntax that looks like this: Range(Cell1, [Cell2]).
• After you’ve picked your cell(s), you tell Excel what you want to put there by using the Value property (.Value) or simply “=”.
• If you want to make sure all cells are empty before you start, you add a command at the start of the subprocedure: Cells.Clear.

Remember, whenever you’re ready to see if your code works, just press the F5 key while you’re in the code editor.

• 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 Function

The OFFSET 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”

How to Debug VBA Code (VBA Debug)

You can easily go through each line of VBA code. 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 with VBA

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.

Summary

Here is a summary for the different methods to write to cells and ranges with Excel VBA.

Published on: April 12, 2018 Last modified: May 30, 2024 Category: Excel,VBA & Scripts Tagged as: ActiveCell, Cell Reference, Debug, Debugging, OFFSET, Range, Selection, VBA, Writing to Cells LinkedIn Twitter YouTube

Leila Gharani

I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.

As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.

I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.

More About Leila Join 400,000+ professionals in our courses