Originally Posted by
Aflatoon
Unless the code is in a worksheet code module, in which case it refers to that sheet, whether it's active or not.
The ws1.Range(ws1.Cells(), ws1.Cells()) version is the one to use - always qualify both Range and Cells.
1. Yes, this example I assumed was in a standard module because of all the .Activate's , but you're right
2. Possible a best practice, but redundant I think if the components can make a valid range on the correct worksheet (fails if they don't)
Option Explicit
'1 - 10 in Sheet2.Range("A1:A10")
Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
ws1.Select
'wrong answer
MsgBox Application.WorksheetFunction.Sum(Range(Cells(1, 1), Cells(1, 1).End(xlDown)))
'right answer
MsgBox Application.WorksheetFunction.Sum(ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)))
MsgBox ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)
'also right answer
MsgBox Application.WorksheetFunction.Sum(Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)))
MsgBox Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)
'fails
' MsgBox Application.WorksheetFunction.Sum(ws1.Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)))
' MsgBox ws1.Range(ws2.Cells(1, 1), ws2.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)
'fails
' MsgBox Application.WorksheetFunction.Sum(Range(ws2.Cells(1, 1), ws1.Cells(1, 1).End(xlDown)))
' MsgBox Range(ws2.Cells(1, 1), ws1.Cells(1, 1).End(xlDown)).Address(1, 1, 1, 1)
End Sub
Just my nit picky opinion