Error on Set Rng statement?

[Steve]

Hi all. Any idea why this line errors out if the sheet Data is not
the active sheet? If I run the code while on the Data sheet, all
works well. If I run thwe code while on any other sheet, I get an
"application defined or object defined error". Thanks!

Set rng = Worksheets("Data").Range(Cells(1, 1), Cells(Rows.Count,
1).End(xlUp))

 

[Gary''s Student]

Sub routine()
Set rng = Worksheets("Data").Range(Cells(1, 1), Cells(Rows.Count,
1).End(xlUp))
MsgBox (rng.Address)
End Sub

will work if:

1. there is a worksheet named Data
2. column A in that sheet has some data in it.

 

[Dave Peterson]

When you have an unqualified range in a general module, it will refer to the
activesheet.

When you have an unqualified range in a worksheet module, it will refer to the
sheet holding the code.

so this will work

Set rng = Worksheets("Data").Range(Worksheets("Data").Cells(1, 1), _
Worksheets("Data").Cells(Rows.Count, 1).End(xlUp))

as will:

with Worksheets("Data")
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

I'd use:

with Worksheets("Data")
Set rng = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
End With

 

[Steve]

Awesome. Thanks guys!!

When you have an unqualified range in a general module, it will refer to the
activesheet.

When you have an unqualified range in a worksheet module, it will refer tothe
sheet holding the code.

so this will work

Set rng = Worksheets("Data").Range(Worksheets("Data").Cells(1, 1), _
              Worksheets("Data").Cells(Rows.Count, 1).End(xlUp))

as will:

with Worksheets("Data")
  Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

I'd use:

with Worksheets("Data")
  Set rng = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
End With