Several ways to make selections using VBA
To work effectively with Excel, you need to be able to refer to the
workbook, sheet, or range of interest. If you use the macro recorder as a
learning tool, you will think that you need to select everything before you
manipulate it. That is not the case.
This page contains a number of methods for working with cells and ranges in
a worksheet. Although it starts with several ways to make selections, there
are two samples at the end that demonstrate how to work without
making selections first.
Finding the last row
To find the last used row in a column, it’s tempting to start at the top
and move down. But if there are any blank cells, you run the risk of making
a mistake. It is safer to ride up from the bottom of the worksheet, until
you encounter a non-blank cell.
Sub FindLastRow()
Dim RwLast As Long
'find the last row
RwLast = Range("A65536").End(xlUp).Row
'now, select from A2 to the last used row
Range("A2:A" & RwLast).Select
End Sub
This method will work well for all versions of Excel up to and
including 2003. Because Excel 2007 has over a million rows in a
worksheet, the following modification (which will work in any
version of Excel) is preferable. Instead of using 65536 (the last
row in Excel 97 – 2003), the modified code uses
Activesheet.Rows.Count.
Sub FindLastRow_Universal()
Dim RwLast As Long
'find the last row
RwLast = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
'now, select from A2 to the last used row
Range("A2:A" & RwLast).Select
End Sub
Selecting the whole worksheet
If you need to do something to the whole worksheet, you can do that using
the Cells method.
This code will select the entire worksheet. It is the VBA equivalent of
pressing Ctrl+A, and if you use the macro recorder you will get this
one-liner:
Cells.Select
Selecting to the next blank row
To select from the active cell to the next blank cell, use the
End method
Range(ActiveCell, ActiveCell.End(xlDown)).Select
The above code is equivalent to pressing Ctrl+Shift+DownArrow.
The
End method lets you select in any of the four directions
xlUp, xlDown, xlToLeft, xlToRight.
But what if you want to select an entire table, and there are some
gaps in the rows or columns? This method will cause trouble if you
have a routine that attempts to select a table where there are
varying numbers of blank cells. You keep needing to check where you
are on the sheet. That’s where the next snippet will help: it’s the
equivalent of pressing
Ctrl+* and it will select a complete
table, including blanks.
ActiveCell.CurrentRegion.Select
Working without selecting
One very powerful feature of VBA is that you generally don’t need
to select objects (e.g., Ranges and Sheets) in order to manipulate
them. This is something that you won’t learn from the macro
recorder: code generated by the recorder invariably involves
selection. In many cases, removing the selection step will
streamline your code. The next samples show recorded coded, and
options for modifying that code to avoid selecting.
Writing and copying formulas
If you have spent some time building a worksheet, you can give
yourself some peace of mind by recording the formulas so that, in
the event of accidental deletion or modification, you can restore
the formulas to their original state. In the following example there
are simple formulas in columns I:L, extending from row 2 to the end
of the table. Once they have been created we can record them using
the following steps:
Recorded code
- Select a cell that doesn’t have the formulas (say, A4) and
start the recorder - Making sure that the recorder is in Absolute mode, select I2
- Press the F2 key, then press Tab; repeat this sequence until
you are in column M - Using the left arrow key, move back to I2
- Hold down Shift, and press the right arrow 3 times to select
I2:L2 - Double-click the fill handle on the selection, and stop the
recorder
Once you are finished, take a look at the code. It will look like
this (comments added):
Sub RecordFormulas()
'Write the formulas in Row 2
Range("I2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=MIN(RC[-8]:RC[-2])"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=MAX(RC[-9]:RC[-3])"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-10]:RC[-4])"
'Fill the formulas down to L25 (the end of the table)
Range("I2:L2").Select
Selection.AutoFill Destination:=Range("I2:L25")
Range("I2:L25").Select
End Sub
Note that the recorder hard-wires the autofill range. Every time
you run this code you will fill down to L25, which was the position
of the last row when the code was recorded. That’s inflexible; if
you add or delete records, you want the code to adjust. So, the
changes we will make are:
Adapting the code
- Remove the selection steps
- Find the last used row in the table, and fill down to there.
This will adjust for differing numbers of rows.
The finished code is shown below.
Sub WriteFormulas()
'Declare a variable to use for the last row of the table
Dim Rw As Long
'Find the last row and pass that value to Rw
Rw = Range("A65536").End(xlUp).Row
'Write the formulas in Row 2
Range("I2").FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])"
Range("J2").FormulaR1C1 = "=MIN(RC[-8]:RC[-2])"
Range("K2").FormulaR1C1 = "=MAX(RC[-9]:RC[-3])"
Range("L2").FormulaR1C1 = "=SUM(RC[-10]:RC[-4])"
'Fill the formulas down to the end of the table
Range("I2:L2").AutoFill Destination:=Range("I2:L" & Rw)
End Sub
Breaking it down
Some comments on the syntax of the line that copies the formulas:
The first part of the line:
Range("I2:L2").AutoFill
If you want to copy down several columns of formulas using
AutoFill, you must include all of the columns in the first
Range reference, as shown above. If you only include a single cell
in the first reference, like:
Range("I2").AutoFill
then you will copy just that formula to all the target
cells.
The second part of the line:
Destination:=Range("I2:L" & Rw)
The address of a range is a string (a piece of text), which is
why you place it in quotes. Like any other string in VBA, you can
build it from components, joining them together with the & operator.
Earlier on in the code we determined the value of Rw, the last used
row in the table. If the table extended down to row 125, then the
range reference would evaluate to
Destination:=Range("I2:L" & 125)
or
Destination:=Range("I2:L125")
That is a valid cell reference, so the Autofill proceeds without
any problems.
Copying cells to another worksheet
Say you have a sheet with 8 columns of data: City, containing
four cities, and 7 sales reps with their unit sales. You want to
loop through Column A and copy all the data for Sydney to Sheet2.
The recorder won’t do the loop so we’ll have to create that
ourselves, but we can record the first step. Here goes, with the
recorder set to Relative references:
Recorded code — one step of the process
Sub CopyPasteRecorded()
'select the cells in the current table row
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
'copy those cells and switch to Sheet2
Selection.Copy
Sheets("Sheet2").Select
'select a cell below the table, then move up to the first blank row
ActiveCell.Offset(19, 0).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
'return to Sheet1 and move down one cell
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
'cancel the copy mode (equivalent to pressing Esc)
Application.CutCopyMode = False
End Sub
Note how many selections need to be made to copy one row to Sheet2,
revert to Sheet1, and move down one line. The modified version has these
changes:
- A For…Next loop is used to define the reference column.
Without needing to select the cells, we can work with all rows
down to the last used row. - We have added a test to see if the reference cell is
“Sydney”. If it is, the range from the reference cell to the
right edge of the table is copied to Sheet2. At no point do we need to
select Sheet2, so we don’t need to spend time switching between the
worksheets. This not only simplifies the code, but also speeds it up. - We have used code to determine which row to paste to. As with the
formula example, we can determine the position of the last row by riding
up from the bottom of the sheet. Because we want to paste the data into
the next row, we use Offset(1,0), which moves down 1 cell from the last
used cell. - No selections are used. Note how much more compact the
finished code is. The recorded version used 11 lines of code to
process one row. The rewritten version uses 7 lines to loop
through the entire table and transfer the relevant data to the
other sheet.
Modified code — the whole loop
Sub CopyPasteModified()
'Declare a Range variable to use in the code
Dim c As Range
'Loop through all cells from A2 down to the last non-blank cell
'in column A.
'In the recorded code, the "bottom" cell was around A20.
'We will change this to A65536 for the final code,
'so that we will always find the last used row
For Each c In Range("A2:A" & Range("A65536").End(xlUp).Row)
'Check that the cell meets the condition: if so, copy.
'You can define the destination without needing to select it.
If c.Value = "Sydney" Then
Range(c, c.End(xlToRight)).Copy _
Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next c
'Cancel copy mode
Application.CutCopyMode = False
'Go to Sheet2 to see the copied rows
Sheets("Sheet2").Activate
End Sub
Wrapping up
This page shows several techniques for selecting ranges of cells.
It also shows how to refer to those cells instead of selecting them,
and provides two examples of the difference between recorded code
(with multiple selection steps) and re-written code that removes the
need to select anything.
