SOLVED - How to avoid using Select in Excel VBA

 

How to avoid using Select in Excel VBA

Examples to avoid select -

Use Dim'd variables

Dim rng as Range

Set the variable to the required range.

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

Ways to refer to a single-cell range 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)

Use the shortcut to the Evaluate method, but this is less efficient

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

If  you specifically want to work only with the active sheet

Dim ws As Worksheet
Set 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,

Set rng = ActiveSheet.Range("A1")
 

You specifically want to work only with the ActiveWorkbook or ThisWorkbook

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

 

 If you do want to work with the ActiveWorkbook

 

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

NOTE- Take care, as many WorkBook methods change the active book

 

 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 

This is better and faster way :

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set 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 perform
next
rng.Value = dat ' put new values back on sheet