Chapter 6
Feeling at Home on the Range
In This Chapter
Selecting ranges
Navigating the cells in your worksheets
Inserting and deleting blank rows and columns
Limiting range movement
Finding and selecting the first blank row or column
One of the most important things you do in Excel is navigate the worksheet. When you work with Excel manually, you're constantly navigating to appropriate ranges, finding the last row, moving to the last column, hiding and unhiding ranges, and so on.
When you attempt to automate your work through VBA, you’ll find that navigating your spreadsheet remains an important part of the automation process. In many cases, you need to dynamically navigate and manipulate Excel ranges, just as you would manually — only through VBA code. This chapter provides some of the most commonly used macros in terms of navigating and working with ranges.
Selecting and Formatting a Range
One of the basic things you need to do in VBA is to select a specific range to do something with it. The simple macro in this section selects the range D5:D16.
How the macro works
In this macro, you explicitly define the range to select by using the Range object:
Sub Macro1()
Range("D5:D16").Select
End Sub
After the range of cells is selected, you can use any of the Range properties to manipulate the cells. The macro has been altered so that the range is colored yellow, converted to number formatting, and bold.
Sub Macro1()
Range("D5:D16").Select
Selection.NumberFormat = "#,##0"
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 36
End Sub
You don’t have to memorize all the properties of the cell object to manipulate them. You can simply record a macro, do your formatting, and then look at the code that Excel has written. After you've seen what the correct syntax is, you can apply it as needed. Many Excel programmers start learning VBA this way.
Note that I refer to Selection several times in the preceding sample code. To write more efficient code, you can simply refer to the range, using the With-End With statement. This statement tells Excel that any action you perform applies to the object to which you’ve pointed. Note that this macro doesn’t select the range. This point is key. In a macro, you can work with a range without selecting it first.
Sub Macro1()
With Range("D5:D16")
.NumberFormat = "#,##0"
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub
Another way you can select a range is by using the Cells item of the Range object. The Cells item gives you a handy way to select ranges through code. It requires only relative row and column positions as parameters. Cells(5,4) translates to row 5, column 4 (or Cell D5). Cells(16, 4) translates to row 16, column 4 (or cell D16).
If you want to select a range of cells, simply pass two items to the Range object. This macro performs the same selection of range D5:D16:
Sub Macro1()
Range(Cells(5, 4), Cells(16, 4)).Select
End Sub
Here is the full formatting code using the Cells item. Again, note that this macro doesn’t select the range you are altering. You can work with a range without selecting it first.
Sub Macro1()
With Range(Cells(5, 4), Cells(16, 4))
.NumberFormat = "#,##0"
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub
How to use the macro
To implement this kind of a macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code into the code window.
Creating and Selecting Named Ranges
One of the more useful features in Excel is the capability to name your range (that is, to give your range a user-friendly name, so that you can more easily identify and refer to it via VBA).
Here are the steps you would perform to create a named range manually.
- Select the range you want to name.
- Go to the Formulas tab in the Ribbon, and choose the Define Name command (see Figure 6-1).
- In the New Name dialog box, give the chosen range a user-friendly name, as shown in Figure 6-2.
- Click OK.
Your range is named.
Figure 6-1: Click the Define Name command to name a chosen range.
Figure 6-2: Give your range a name.
To confirm that your named range was created properly, you can go to the Formula tab and select the Name Manager command. The Name Manager dialog box appears, as shown in Figure 6-3, and you can see all applied named ranges.
Figure 6-3: The Name Manager dialog box lists all applied named ranges.
Creating a named range through VBA is much less involved. You can directly define the Name property of the Range object:
Sub Macro1()
Range("I1:J17").Name = "MyData"
End Sub
Admittedly, you’d be hard-pressed to find a situation where you would need to automate the creation of named ranges. The real efficiency comes in manipulating them through VBA.
How the macro works
In this macro, you simply pass the name of the range through the Range object. This object allows you to select the range:
Sub Macro1()
Range("MyData").Select
End Sub
As with normal ranges, you can refer to the range using the With-End With statement, which tells Excel that any action you perform applies to the object to which you’ve pointed. This technique not only prevents you from having to repeat syntax but also allows for the easy addition of actions by simply adding them between the With and End With statements.
Sub Macro1()
With Range("MyData")
.NumberFormat = "#,##0"
.Font.Bold = True
.Interior.ColorIndex = 36
End With
End Sub
How to use the macro
To implement this kind of a macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code.
Enumerating through a Range of Cells
One must-have VBA skill is the capability to enumerate (or loop) through a range. If you do any serious macro work in Excel, you'll soon encounter the need to go through a range of cells one by one and perform some action.
The basic macro in this section shows you a simple way to enumerate through a range.
How the macro works
This macro uses two Range object variables. One variable captures the scope of data you're working with, and the other holds each individual cell as you go through the range. Then you use the For Each statement to activate, or bring into focus, each cell in the target range:
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
'Step 2: Define the target range
Set MyRange = Range("D6:D17")
'Step 3: Start looping through the range
For Each MyCell In MyRange
'Step 4: Do something with each cell
If MyCell.Value > 3000 Then
MyCell.Font.Bold = True
End If
'Step 5: Get the next cell in the range
Next MyCell
End Sub
The macro first declares two Range object variables. MyRange holds the entire target range, and MyCell holds each cell in the range as the macro enumerates through them one by one.
In Step 2, you fill the MyRange variable with the target range. In this example, you use Range(“D6:D17”). If your target range is a named range, you could simply enter its name — Range(“MyNamedRange”).
In Step 3, the macro starts looping through each cell in the target range, activating each cell as it goes through.
After a cell is activated, you would do something with it. That “something” depends on the task at hand. You may want to delete rows when the active cell has a certain value, or you may want to insert a row between each active cell. In Step 4 of this example, the macro is changing the font to Bold for any cell that has a value greater than 3,000.
In Step 5, the macro loops back to get the next cell. After all cells in the target range are activated, the macro ends.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code.
Inserting Blank Rows in a Range
Occasionally, you may need to dynamically insert rows into your data set. Although blank rows are generally bothersome, in some situations the final formatted version of your report requires blank rows to separate data. The macro in this section adds blank rows into a range.
How the macro works
This macro performs a reverse loop through the chosen range by using a counter. It starts at the last row of the range, inserting two blank rows, and then moves to the previous row in the range. It keeps doing the same insert for every loop, each time incrementing the counter to the previous row.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim iCounter As Long
'Step 2: Define the target range
Set MyRange = Range("C6:D17")
'Step 3: Start reverse looping through the range
For iCounter = MyRange.Rows.Count To 2 Step -1
'Step 4: Insert two blank rows
MyRange.Rows(iCounter).EntireRow.Insert
MyRange.Rows(iCounter).EntireRow.Insert
'Step 5: Go to the next counter number
Next iCounter
End Sub
First, you declare two variables. The first variable is an Object variable called MyRange that defines the target range. The other variable is a Long Integer variable called iCounter that serves as an incremental counter.
In Step 2, the macro fills the MyRange variable with the target range. In this example, you use Range(“C6:D17”). If your target range is a named range, you could simply enter its name — Range(“MyNamedRange”). The macro sets the parameters for the incremental counter to start at the max count for the range (MyRange.Rows.Count) and end at 2 (the second row of the chosen range). Note that you are using the Step-1 qualifier, so Excel knows that you will adjust the counter backwards, moving back one increment on each iteration. In all, Step 3 tells Excel to start at the last row of the chosen range, moving backward until it gets to the second row of the range.
When working with a range, you can explicitly call out a specific row in the range by passing a row index number to the Rows collection of the range. For instance, Range(“D6:D17”).Rows(5) points to the fifth row in the range D6:D17.
In Step 4, the macro uses the iCounter variable as an index number for the Rows collection of MyRange. This variable helps pinpoint the exact row that the macro is working with in the current loop. The macro then uses the EntireRow.Insert method to insert a new blank row. Because you want two blank rows, you apply the EntireRow.Insert method twice.
In Step 5, the macro loops back to move to the next counter number.
How to use the macro
To implement this macro, you can copy and paste it into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code.
Unhiding All Rows and Columns
When you're auditing a spreadsheet that you did not create, you often want to ensure that you’re getting a full view of the spreadsheet's contents. To do so, all columns and rows must not be hidden. This simple macro automatically unhides all rows and columns for you.
How the macro works
In this macro, you call on the Columns collection and the Rows collection of the worksheet. Each collection has properties that dictate where their objects are hidden or visible. Running this macro unhides every column in the Columns collection and every row in the Rows collection.
Sub Macro1()
Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False
End Sub
How to use the macro
The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Then simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Deleting Blank Rows
Work with Excel long enough, and you’ll find that blank rows can often cause havoc on many levels. They can create problems with formulas, introduce risk when copying and pasting, and sometimes cause strange behaviors in pivot tables. If you find that you are manually searching out and deleting blank rows in your data sets, the macro in this section can help automate the task.
How the macro works
In this macro, you use the UsedRange property of the ActiveSheet object to define the range you are working with. The UsedRange property gives you a range that encompasses the cells that have been used to enter data. You then establish a counter that starts at the last row of the used range and checks to see if the entire row is empty. If the entire row is indeed empty, you remove the row. You keep doing the same delete for every loop, each time incrementing the counter to the previous row.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim iCounter As Long
'Step 2: Define the target range
Set MyRange = ActiveSheet.UsedRange
'Step 3: Start reverse looping through the range
For iCounter = MyRange.Rows.Count To 1 Step -1
'Step 4: If entire row is empty delete it
If Application.CountA(Rows(iCounter).EntireRow) = 0 Then
Rows(iCounter).Delete
End If
'Step 5: Move to the next counter number
Next iCounter
End Sub
The macro first declares two variables. The first variable is an Object variable called MyRange, which defines the target range. The other variable is a Long Integer variable called iCounter, which serves as an incremental counter.
In Step 2, the macro fills the MyRange variable with the UsedRange property of the ActiveSheet object. The UsedRange property gives you a range that encompasses the cells that have been used to enter data. Note that if you wanted to specify an actual range or a named range, you could simply enter its name — Range(“MyNamedRange”).
In this step, the macro sets the parameters for the incremental counter to start at the max count for the range (MyRange.Rows.Count) and end at 1 (the first row of the chosen range). Note that you use the Step-1 qualifier, so Excel knows you are going to adjust the counter backwards, moving back one increment on each iteration. In all, Step 3 tells Excel to start at the last row of the chosen range and move backward until it gets to the first row of the range.
When working with a range, you can explicitly call out a specific row in the range by passing a row index number to the Rows collection of the range. For instance, Range(“D6:D17”).Rows(5) points to the fifth row in the range D6:D17.
In Step 4, the macro uses the iCounter variable as an index number for the Rows collection of MyRange. This variable helps pinpoint the row you are working with in the current loop. The macro checks to see whether the cells in that row are empty. If they are, the macro deletes the entire row.
In Step 5, the macro loops back to move to the next counter number.
How to use the macro
The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Next, record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Deleting Blank Columns
Just as with blank rows, blank columns have the potential of causing unforeseen errors. If you find that you are manually searching for and deleting blank columns in your data sets, use the macro in this section to automate that task.
How the macro works
In this macro, you use the UsedRange property of the ActiveSheet object to define the range you are working with. The UsedRange property gives you a range that encompasses the cells that have been used to enter data. You then establish a counter that starts at the last column of the used range, checking to see if the entire column is empty. If the entire column is indeed empty, you remove the column. You keep doing the same delete for every loop, each time incrementing the counter to the previous column.
Sub Macro1()
'Step 1: Declare your variables
Dim MyRange As Range
Dim iCounter As Long
'Step 2: Define the target range
Set MyRange = ActiveSheet.UsedRange
'Step 3: Start reverse looping through the range
For iCounter = MyRange.Columns.Count To 1 Step -1
'Step 4: If entire column is empty delete it
If
Application.CountA(Columns(iCounter).EntireColumn) = 0 Then
Columns(iCounter).Delete
End If
'Step 5: Move to the next counter number
Next iCounter
End Sub
You first declare two variables. The first variable is an Object variable called MyRange, which defines the target range. The other variable is a Long Integer variable called iCounter, which serves as your incremental counter.
In Step 2, you fill the MyRange variable with the UsedRange property of the ActiveSheet object. The UsedRange property gives you a range that encompasses the cells that have been used to enter data. Note that if you wanted to specify an actual range or a named range, you could simply enter its name — Range(“MyNamedRange”).
In this step, you set the parameters for your incremental counter to start at the max count for the range (MyRange.Columns.Count) and end at 1 (the first row of the chosen range). Note that you are using the Step-1 qualifier, so Excel knows that you will increment the counter backwards, moving back one increment on each iteration. In all, Step 3 tells Excel that you want to start at the last column of the chosen range and move backward until you get to the first column of the range.
When working with a range, you can explicitly call out a specific column in the range by passing a column index number to the Columns collection of the range. For instance, Range(“A1:D17”).Columns(2) points to the second column in the range (column B).
In Step 4, the macro uses the iCounter variable as an index number for the Columns collection of MyRange. This variable helps pinpoint the column you are working with in the current loop. The macro checks to see whether all the cells in that column are empty. If they are, the macro deletes the entire column.
In Step 5, the macro loops back to increment the counter down.
How to use the macro
The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In VBE project window, it is named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select Personal Macro Workbook. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Limiting Range Movement to a Particular Area
Excel gives you the ability to limit the range of cells that a user can scroll through. The macro demonstrated in this section is one you can easily implement today.
How the macro works
Excel’s ScrollArea property allows you to set the scroll area for a particular worksheet. For instance, the following statement sets the scroll area on Sheet1 so the user cannot activate any cells outside A1:M17:
Sheets("Sheet1").ScrollArea = "A1:M17"
Because this setting is not saved with a workbook, you’ll have to reset it each time the workbook is opened. You can accomplish this task by implementing the following statement in the Workbook_Open event:
Private Sub Worksheet_Open()
Sheets("Sheet1").ScrollArea = "A1:M17"
End Sub
If for some reason you need to clear the scroll area limits, you can remove the restriction with this statement:
ActiveSheet.ScrollArea = ""
How to use the macro
To implement this macro, you will need to copy and paste it into the Workbook_Open event code window. By placing the macro here, you allow it to run each time the workbook opens.
- Activate Visual Basic Editor by pressing Alt+F11.
- In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
- Click ThisWorkbook.
- In the Event drop-down box, select the Open event (see Figure 6-4).
- Type or paste the code.
Figure 6-4: Enter your code in the Workbook Open event.
Selecting and Formatting All Formulas in a Workbook
When auditing an Excel workbook, it’s paramount to have a firm grasp of all the formulas in each sheet. This means finding all the formulas, which can be an arduous task if performed manually.
However, Excel provides a slick way of finding and tagging all formulas in a worksheet. The macro in this section exploits this functionality to dynamically find all cells that contain formulas.
How the macro works
Excel has a set of predefined special cells that you can select by using the Go to Special dialog box. To select special cells manually, go to the Home tab on the Ribbon and select Go to Special. The Go to Special dialog box appears, as shown in Figure 6-5.
Figure 6-5: The Go to Special dialog box.
In this dialog box, you can select a set of cells based on a few defining attributes, including formulas. Selecting the Formulas option effectively selects all cells that contain formulas (see Figure 6-6). At this point, you can color the cells to indicate they contain a formula.
Figure 6-6: Choose Formulas to tell Excel to select all cells containing a formula.
The macro programmatically does the same thing for the entire workbook at the same time. Here, you use the SpecialCells method of the Cells collection. The SpecialCells method requires a type parameter that represents the type of special cell. In this case, you're using xlCellTypeFormulas.
In short, you refer to a special range that consists only of cells that contain a formula. You refer to this special range using the With-End With statement, which tells Excel that any action you perform applies only to the range to which you’ve pointed. Here, you're coloring the interior of the cells in the chosen range.
Sub Macro1()
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Avoid error if no formulas are found
On Error Resume Next
'Step 3: Start looping through worksheets
For Each ws In ActiveWorkbook.Worksheets
'Step 4: Select cells and highlight them
With ws.Cells.SpecialCells(xlCellTypeFormulas)
.Interior.ColorIndex = 36
End With
'Step 5: Get next worksheet
Next ws
End Sub
Step 1 declares an object called ws. This step creates a memory container for each worksheet the macro loops through.
If no formulas are in the spreadsheet, Excel will throw an error. In Step 2 you tell Excel to continue with the macro if an error is triggered.
Step 3 begins the looping, telling Excel to evaluate all worksheets in the active workbook.
In Step 4, the macro selects all cells containing formulas and then formats them.
In Step 5, you loop back to get the next sheet. After all sheets are evaluated, the macro ends.
How to use the macro
The best place to store this macro is in your personal macro workbook so that the macro is always available. The personal macro workbook is loaded whenever you start Excel. In VBE project window, it's named personal.xlsb.
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click personal.xlb in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code.
If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.
To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select Personal Macro Workbook. Then record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.
Finding and Selecting the First Blank Row or Column
You may often run across scenarios where you have to append rows or columns to an existing data set. When you want to append rows, you'll need to be able to find the last used row and then move down to the next empty cell (as shown in Figure 6-7). Likewise, when you want to append columns, you need to be able to find the last used column and then move over to the next empty cell.
Figure 6-7: Use a macro to dynamically find the first available cell in a row or a column.
The macros in this section allow you to dynamically find and select the first blank row or column.
How the macro works
These macros both use the Cells item and the Offset property as key navigation tools.
The Cells item belongs to the Range object and provides a handy way to select ranges through code. It requires only relative row and column positions as parameters. Cells(5,4) translates to row 5, column 4 (or Cell D5). Cells(16, 4) translates to row 16, column 4 (or cell D16).
In addition to passing hard numbers to the Cells item, you can also pass expressions.
Cells(Rows.Count, 1) is the same as selecting the last row in the spreadsheet and the first column in the spreadsheet. In Excel, that essentially translates to cell A1048576.
Cells(1, Columns.Count) is the same as selecting the first row in the spreadsheet and the last column in the spreadsheet. In Excel, that translates to cell XFD1.
Combining the Cells statement with the End property enables Excel to jump to the last used row or column. This statement is equivalent to going to cell A1048576 and pressing Ctrl+Shift+up arrow on the keyboard. When you run this line of code, Excel automatically jumps to the last used row in column A:
Cells(Rows.Count, 1).End(xlUp).Select
Running this statement is equivalent to going to cell XFD1 and pressing Ctrl+Shift+left arrow on the keyboard. This line of code gets you to the last used column in row 1:
Cells(1, Columns.Count).End(xlToLeft).Select
When you get to the last used row or column, you can use the Offset property to move down or over to the next blank row or column. The Offset property uses a row and column index to specify a changing base point.
For example, the following statement selects cell A2 because the row index in the offset is moving the row base point by 1:
Range("A1").Offset(1, 0).Select
This statement selects cell C4 because the row and column indexes move the base point by three rows and two columns:
Range("A1").Offset(3, 2).Select
Pulling all these concepts together, you can create a macro that selects the first blank row or column.
This macro selects the first blank row:
Sub Macro1()
'Step 1: Declare your variables
Dim LastRow As Long
'Step 2: Capture the last-used row number
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Step 3: Select the next row down
Cells(LastRow, 1).Offset(1, 0).Select
End Sub
First, you declare a Long Integer variable called LastRow to hold the row number of the last used row.
In Step 2, you capture the last used row by starting at the last row in the worksheet and using the End property to jump up to the first nonempty cell (the equivalent of going to cell A1048576 and pressing Ctrl+Shift+up arrow).
In Step 3, you use the Offset property to move down one row and select the first blank cell in column A.
This macro selects the first blank column:
Sub Macro1()
'Step 1: Declare your variables
Dim LastColumn As Long
'Step 2: Capture the last-used column number
LastColumn = Cells(5, Columns.Count).End(xlToLeft).Column
'Step 3: Select the next column over
Cells(5, LastColumn).Offset(0, 1).Select
End Sub
First, you declare a Long Integer variable called LastColumn to hold the column number of the last used column.
In Step 2, you capture the last used column by starting at the last column in the worksheet and using the End property to jump up to the first nonempty column (the equivalent of going to cell XFD5 and pressing Ctrl+Shift+left arrow).
In Step 3, you use the Offset property to move over one column and select the first blank column in row 5.
How to use the macro
You can implement these macros by pasting them into a standard module:
- Activate Visual Basic Editor by pressing Alt+F11.
- Right-click the project/workbook name in the project window.
- Choose Insert ⇒ Module.
- Type or paste the code.