How to set Range from activesheet (or any other)

Question

I'm confused about how VBA handles Range in different situations..

I have 2 sheets "Main" and "Data". There is button on the sheet "Main" which includes following code:

Worksheets("Data").Activate
Worksheets("Data").Select
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row    
Dim SrchRange As Range
Set SrchRange = Range(Cells(1, 1), Cells(LastRow, 7))

As it is SrchRange will refer to the sheet "Main" even if the "Data" sheet is activated and selected. For the "LastRow" I found this workaround to put it as ActiveSheet.Cells(... but for "SrchRange" I can't find the way to do it. I have tried at least:

Set SrchRange = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, 7))
Set SrchRange = Sheets("Data").Range(Cells(1, 1), Cells(LastRow, 7))
Set SrchRange = Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(LastRow, 7))

All of them will give me a runtime error '1004'. I don't understand how the situation changes if Range is described in more detail. Shouldn't it still be range even with the Sheets and ActiveSheet? How should I accomplish this?

I managed to circumvent the issue by not using the Range variable at all. Here is the section where the SrchRange variable is used.

For Each HeaderMatch In SrchRange
If HeaderMatch Like HeaderKeyWord Then

Which I replaced with this:

For Each HeaderMatch In ActiveSheet.Range(ActiveSheet.Cells(1, 1), 
ActiveSheet.Cells(LastRow, 7))
If HeaderMatch Like HeaderKeyWord Then

I would prefer the first one as it is little bit more clear and out of principle I want to get it work as I intended because I know it is possible but I don't know how.

I'm not quite getting the concept of ActiveSheet and selecting the sheets. What is the point of activating the sheet if I still need to refer to it as ActiveSheet? Why not to use just Sheets() to select specific sheet if you anyways need to alway use the reference. And to stir it even more what is the use of Select?

Answer 1

I had this problem myself. The reason is because the .End() method only works on the active sheet.

Per Microsoft's documentation the .End() is equivalent to pressing end+↑, end+↓, end+← or end+→. Read-only Range object. This is why setting a range with Set Range1 = Sheets("Data").Range(("A1"),Range("A1").End(Xldown)) only works if data is currently selected.

As a workaround use the .Activate method on your data sheet to set the range.

Answer 2

It is confusing using ActiveSheet when dealing with 2 or more worksheet at the same time. It is better to set a worksheet to a variable such as code below:

Dim ws1 as Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Then do your stuff with these two variable pointed to a worksheet. In addition, using a With block would make the code clearer to tell which worksheet your are dealing. Therefore the code you given can be re-written as below:

Dim wsData As Worksheet
Set wsData = Worksheets("Data")
With wsData
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Dim SrchRange As Range
    Set SrchRange = .Range(.Cells(1, 1), .Cells(LastRow, 7))
End With

I gave some testings for ActiveSheet and calling collection without specifying certain reference. Say I have a Workbook with 2 Worksheets, one named SheetA and another is SheetB. The code below is put in SheetA.

Private Sub test()
    Worksheets("B").Activate
    MsgBox Range("A1").Parent.Name
    MsgBox ActiveSheet.Range("A1").Parent.Name
End Sub

Result is that the first MsgBox gives SheetA, and the second one gives SheetB. Therefore I assume that if you called a collection without specifying reference, the default is the one where the marco is called.

I then put the code in a module. This time both MsgBox gives the same result SheetB no matter which worksheet is active before executing the code. So here is my conclusion.

1. Calling a collection without specifying reference, the default is where the code belongs to.
2. When in a module level, the default is the active sheet currently.
3. Specifying the reference is a good manner and can avoid some unpredictable errors. Note it's better to specify even the Workbook when dealing with several workbooks at the same time.

Let's take a look at these three lines you asked:

1. Set SrchRange = Range(Cells(1, 1), Cells(LastRow, 7))

All three collections, i.e. Range, Cells and Cells. are from the same parent, the code belongs to or the activesheet, depends on where the code is. So it will always pass the compiler, but not always giving you the correct result.

2. Set SrchRange = Sheets("Data").Range(Cells(1, 1), Cells(LastRow, 7))

The Range collection is from Sheets("Data"), but both Cells may come from another sheet, so sometimes it gives an error.

3. Set SrchRange = Range(Sheets("Data").Cells(1, 1), Sheets("Data").Cells(LastRow, 7))

Both Cells are from the same sheet, however the parent of Range depends. So it is the same as [2], sometimes it pass, and sometimes it fail.

Answer 3

Thanks for the quick replies. Two previous answers seems to do the trick. I got the code working by defining Worksheets as variables and manipulating them via with block.

I'm still quite puzzled how this all works. Could somebody give some insight on how these different statements are different?

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastRow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row

Those both seem to work ok. First one is fetching the value from "Main" sheet even if the "Data" sheet is activated. Second one will give lastrow of "Data" sheet. Altogether they both work even if the result is different.

Set SrchRange = Range(Cells(1, 1), Cells(LastRow, 7))
Set SrchRange = Sheets("Data").Range(Cells(1, 1), Cells(LastRow, 7))
Set SrchRange = Range(Sheets("Data").Cells(1, 1), Sheets("Data").Cells(LastRow, 7))

But for these three only first one works but others will give runtime error. I also tried without Set and without predefining the variable to be As Range like the LastRow is.

I think the issue is that a statement is no longer range if for example Sheets("Data"). is added. My variable should probably be something else then? How the LastRow variable seems to work even if Sheets("Data"). is added?

Answer 4

If all the worksheets are in the workbook that contains the vba code, you can use the worksheet code name and not have to set to individual variables.

Example I would change the code name of worksheet "Data" to cnData and use this...

With cnData
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Dim SrchRange As Range
    Set SrchRange = .Range(.Cells(1, 1), .Cells(LastRow, 7))
End With