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 = 5This adjusts the first column to the third column
Autofit can also be done by using Cells.Columns.AutoFit.
Summary
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
