Could anyone tell me why when I refer to a particular range it works fine:
ActiveSheet.Range("A1:D3").Select
but
ActiveSheet.Range(Cells(1, 1), Cells(3, 4)).Select
not working?
Asked
Active
Viewed 5.6k times
0
-
1What do you mean by not working? You get an error? – ChipsLetten May 20 '15 at 19:01
-
Does your actual code use a different sheet than `ActiveSheet`? – Degustaf May 20 '15 at 19:02
-
If your `ActiveSheet` is a chart, it will fail (but so will the first line also). Can't imagine any other reason. Most people get messed up here using `Cells` without a sheet qualifier to access cells on another sheet. You're using the `ActiveSheet` so that issue is removed. – Byron Wall May 20 '15 at 19:27
-
@Byron Unless its a simplification to post the question, and there is another worksheet involved. – Degustaf May 20 '15 at 19:28
-
This has been asked and answered so many times before in Stackoverflow. [Here](http://stackoverflow.com/questions/21678304/method-cells-of-object-global-failed-vba) is one such quesiton. – Siddharth Rout May 20 '15 at 19:56
-
Another [one](http://stackoverflow.com/questions/18962890/2-ways-for-clearcontents-on-vba-excel-but-1-work-fine-why) Question is different but addresses the same issue – Siddharth Rout May 20 '15 at 19:57
1 Answers
3
I suspect your code is in the worksheet code module of a different sheet, so the unqualified Cells calls refer to that sheet, not the active one. You should always qualify all Range or Cells calls with a Worksheet object:
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(3, 4)).Select
This also works avoiding the need to repeat several times the target worksheet: (see https://msdn.microsoft.com/EN-US/library/office/gg264723.aspx)
With ActiveSheet
.Range(.Cells(1, 1), .Cells(3, 4)).Select
End With
