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.