How to avoid using Select in Excel VBA

Some examples of how to avoid select

Use Dim'd variables

Dim rng as Range

Set the variable to the required range. There are many ways to refer to a single-cell range:

Set rng = Range("A1")Set rng = Cells(1, 1)Set rng = Range("NamedRange")

Or a multi-cell range:

Set rng = Range("A1:B10")Set rng = Range("A1", "B10")Set rng = Range(Cells(1, 1), Cells(10, 2))Set rng = Range("AnotherNamedRange")Set rng = Range("A1").Resize(10, 2)

You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code.

Set rng = [A1]Set rng = [A1:B10]

All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet variable too:

Dim ws As WorksheetSet ws = Worksheets("Sheet1")Set rng = ws.Cells(1, 1)With ws    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))End With

If you do want to work with the ActiveSheet, for clarity it's best to be explicit. But take care, as some Worksheet methods change the active sheet.

Set rng = ActiveSheet.Range("A1")

Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook, it is better to Dim a Workbook variable too.

Dim wb As WorkbookSet wb = Application.Workbooks("Book1")Set rng = wb.Worksheets("Sheet1").Range("A1")

If you do want to work with the ActiveWorkbook, for clarity it's best to be explicit. But take care, as many WorkBook methods change the active book.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

You can also use the ThisWorkbook object to refer to the book containing the running code.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

A common (bad) piece of code is to open a book, get some data then close again

This is bad:

Sub foo()    Dim v as Variant    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")    v = ActiveWorkbook.Sheets(1).Range("A1").Value    Workbooks("SomeAlreadyOpenBook.xlsx").Activate    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v    Workbooks(2).Activate    ActiveWorkbook.Close()End Sub

And it would be better like:

Sub foo()    Dim v as Variant    Dim wb1 as Workbook    Dim  wb2 as Workbook    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")    v = wb2.Sheets("SomeSheet").Range("A1").Value    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v    wb2.Close()End Sub

Pass ranges to your Subs and Functions as Range variables:

Sub ClearRange(r as Range)    r.ClearContents    '....End SubSub MyMacro()    Dim rng as Range    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")    ClearRange rngEnd Sub

You should also apply Methods (such as Find and Copy) to variables:

Dim rng1 As RangeDim rng2 As RangeSet rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")rng1.Copy rng2

If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that:

Dim dat As VariantDim rng As RangeDim i As LongSet rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)for i = LBound(dat, 1) to UBound(dat, 1)    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to performnextrng.Value = dat ' put new values back on sheet

This is a small taster for what's possible.

Two main reasons why .Select, .Activate, Selection, Activecell, Activesheet, Activeworkbook, etc. should be avoided

1. It slows down your code.
2. It is usually the main cause of runtime errors.

How do we avoid it?

1) Directly work with the relevant objects

Consider this code

Sheets("Sheet1").ActivateRange("A1").SelectSelection.Value = "Blah"Selection.NumberFormat = "@"

This code can also be written as

With Sheets("Sheet1").Range("A1")    .Value = "Blah"    .NumberFormat = "@"End With

2) If required declare your variables. The same code above can be written as

Dim ws as worksheetSet ws = Sheets("Sheet1")With ws.Range("A1")    .Value = "Blah"    .NumberFormat = "@"End With

That's a good answer, but what I am missing on this topic is when we actually need Activate. Everyone says it is bad, but no one explains any cases where it makes sense to use it.

Situation when you can't avoid using .Activate/.Select. (Will add more links as and when I come across them)

1. When you want to present a worksheet to a user so that the user can see it.
2. Scenarios like Working macro returns error when run from form control where you are forced to use .Activate

One small point of emphasis I'll add to all the excellent answers given previously:

Probably the biggest thing you can do to avoid using Select is to as much as possible, use named ranges (combined with meaningful variable names) in your VBA code. This point was mentioned above, but it was glossed over a bit; however, it deserves special attention.

Here are a couple of additional reasons to make liberal use of named ranges, though I am sure I could think of more.

Named ranges make your code easier to read and understand.

Example:

Dim Months As RangeDim MonthlySales As RangeSet Months = Range("Months")' E.g, "Months" might be a named range referring to A1:A12Set MonthlySales = Range("MonthlySales")' E.g, "Monthly Sales" might be a named range referring to B1:B12Dim Month As RangeFor Each Month in Months    Debug.Print MonthlySales(Month.Row)Next Month

It is pretty obvious what the named ranges Months and MonthlySales contain, and what the procedure is doing.

Why is this important? Partially because it is easier for other people to understand it, but even if you are the only person who will ever see or use your code, you should still use named ranges and good variable names because you will forget what you meant to do with it a year later, and you will waste 30 minutes just figuring out what your code is doing.

Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes.

Consider, if the above example had been written like this:

Dim rng1 As RangeDim rng2 As RangeSet rng1 = Range("A1:A12")Set rng2 = Range("B1:B12")Dim rng3 As RangeFor Each rng3 in rng1    Debug.Print rng2(rng3.Row)Next rng3

This code will work just fine at first - that is until you or a future user decides "gee wiz, I think I'm going to add a new column with the year in Column A!", or put an expenses column between the months and sales columns, or add a header to each column. Now, your code is broken. And because you used terrible variable names, it will take you a lot more time to figure out how to fix it than it should take.

If you had used named ranges to begin with, the Months and Sales columns could be moved around all you like, and your code would continue working just fine.