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 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
, 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 Workbook
Set 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 Sub
s and Function
s as Range variables:
Sub ClearRange(r as Range)
r.ClearContents
'....
End Sub
Sub MyMacro()
Dim rng as Range
Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
ClearRange rng
End Sub
You should also apply Methods (such as Find
and Copy
) to variables:
Dim rng1 As Range
Dim rng2 As Range
Set 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 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
This is a small taster for what's possible.
Best Answer
NOTE: I intend to make this a "one stop post" where you can use the
Correct
way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.Unreliable ways of finding the last row
Some of the most common ways of finding last row which are highly unreliable and hence should never be used.
UsedRange
should NEVER be used to find the last cell which has data. It is highly unreliable. Try this experiment.Type something in cell
A5
. Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cellA10
red. If you now use the any of the below code, you will still get 5. If you useUsedrange.Rows.Count
what do you get? It won't be 5.Here is a scenario to show how
UsedRange
works.xlDown
is equally unreliable.Consider this code
What would happen if there was only one cell (
A1
) which had data? You will end up reaching the last row in the worksheet! It's like selecting cellA1
and then pressing End key and then pressing Down Arrow key. This will also give you unreliable results if there are blank cells in a range.CountA
is also unreliable because it will give you incorrect result if there are blank cells in between.And hence one should avoid the use of
UsedRange
,xlDown
andCountA
to find the last cell.Find Last Row in a Column
To find the last Row in Col E use this
If you notice that we have a
.
beforeRows.Count
. We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using.
beforeRows.Count
andColumns.Count
. That question is a classic scenario where the code will fail because theRows.Count
returns65536
for Excel 2003 and earlier and1048576
for Excel 2007 and later. SimilarlyColumns.Count
returns256
and16384
, respectively.The above fact that Excel 2007+ has
1048576
rows also emphasizes on the fact that we should always declare the variable which will hold the row value asLong
instead ofInteger
else you will get anOverflow
error.Note that this approach will skip any hidden rows. Looking back at my screenshot above for column A, if row 8 were hidden, this approach would return
5
instead of8
.Find Last Row in a Sheet
To find the
Effective
last row in the sheet, use this. Notice the use ofApplication.WorksheetFunction.CountA(.Cells)
. This is required because if there are no cells with data in the worksheet then.Find
will give youRun Time Error 91: Object Variable or With block variable not set
Find Last Row in a Table (ListObject)
The same principles apply, for example to get the last row in the third column of a table: