DataWright Information Services

Consulting and Resources for Excel and Access




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.