worksheet range qualifier

[sugargenius]

Are the last 2 statements equivalent?

Dim oReportSheet As Worksheet
oReportSheet = Worksheets.Add
oReportSheet.Range(Cells(1, 1), Cells(1,
7)).Columns.EntireColumn.AutoFit
Range(oReportSheet.Cells(1, 1), oReportSheet.Cells(1,
7)).Columns.EntireColumn.AutoFit

What if the oReportSheet sheet weren't active. Would that matter?

 

[paul.robinson]

Are the last 2 statements equivalent?

Dim oReportSheet As Worksheet
oReportSheet = Worksheets.Add
oReportSheet.Range(Cells(1, 1), Cells(1,
7)).Columns.EntireColumn.AutoFit
Range(oReportSheet.Cells(1, 1), oReportSheet.Cells(1,
7)).Columns.EntireColumn.AutoFit

What if the oReportSheet sheet weren't active. Would that matter?

They are if oReportSheet is active. So are

Range(Cells(1, 1), Cells(1, 7)).Columns.EntireColumn.AutoFit

and

Activesheet.Range(Cells(1, 1), Cells(1,
7)).Columns.EntireColumn.AutoFit

If oReportSheet is not active, or may not be, the neatest syntax to
use is

With oReportSheet
.Range(.Cells(1, 1), .Cells(1, 7)).Columns.EntireColumn.AutoFit
end With

note the dots before Range and Cells. you must fully qualify the range
reference with the sheet object.

regards
Paul

 

[sugargenius]

They are if oReportSheet is active. So are

Range(Cells(1, 1), Cells(1, 7)).Columns.EntireColumn.AutoFit

and

Activesheet.Range(Cells(1, 1), Cells(1,
7)).Columns.EntireColumn.AutoFit

If oReportSheet is not active, or may not be, the neatest syntax to
use is

With oReportSheet
.Range(.Cells(1, 1), .Cells(1, 7)).Columns.EntireColumn.AutoFit
end With

note the dots before Range and Cells. you must fully qualify the range
reference with the sheet object.

regards
Paul

thanks, Paul