Is the . in .Range necessary when defined by .Cells?

by

My opinion is slightly different here.

YES it is required. You can’t always control where the user may run the code from.

Please consider these few test cases

SCENARIO

Workbook has 2 worksheets. Sheet1 and Sheet2

TEST 1 (Running from a module)

Both Code give same result

TEST 2 (Running from a Sheet code area of Sheet1)

Both Code give same result

TEST 3 (Running from a Sheet code area of Sheet2)

'~~> This code fails
set rng = range(.cells(2, 1), .cells(rows.count, 1).end(xlup))

You will get Application Defined or Object defined error

enter image description here

And hence it is always advisable to properly qualify your objects so that the code can run from anywhere

More Related Contents:

  1. Convert an entire range to uppercase without looping through all the cells
  2. Detect if range is empty
  3. How to copy only values in excel vba from a range?
  4. Copy a selected range to another worksheet
  5. VBA Dialog box to select range in different workbook
  6. VBA: Modify chart data range
  7. SumIf using Excel VBA
  8. How can I call a macro which contains a loop but only run one iteration of that loop?
  9. Test or check if sheet exists
  10. How can I find last row that contains data in a specific column?
  11. Is there any event that fires when keys are pressed when editing a cell?
  12. Does the VBA “And” operator evaluate the second argument when the first is false?
  13. Split cell values into multiple rows and keep other data
  14. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  15. Is VBA an OOP language, and does it support polymorphism?
  16. Export each sheet to a separate csv file
  17. What are .NumberFormat Options In Excel VBA?
  18. Excel: How to check if a cell is empty with VBA? [duplicate]
  19. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  20. Extract Data from Word Document to an Excel SpreadSheet
  21. Excel macro -Split comma separated entries to new rows [duplicate]
  22. VBA equivalent to Excel’s mod function
  23. Get text from clipboard using GetText – avoid error on empty clipboard
  24. Display milliseconds in Excel
  25. VBA editor auto-deletes spaces at the ends of lines
  26. Select entire column in table using Excel VBA
  27. Application.Quit command not closing the entire Excel Application
  28. Reducing WithEvent declarations and subs with VBA and ActiveX
  29. Trying to open the workbook in separate instance
  30. Function Overloading and UDF in Excel VBA

Leave a Comment