worksheet range qualifier

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sugargenius@gmail.com - 14 Dec 2007 14:11 GMT

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?

Reply to this Message

paul.robinson@it-tallaght.ie - 14 Dec 2007 15:24 GMT

On Dec 14, 2:11 pm, "sugargen...@gmail.com" <sugargen...@gmail.com>
wrote:

> Are the last 2 statements equivalent?
>
[quoted text clipped - 6 lines]
>
> 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

Reply to this Message

sugargenius@gmail.com - 14 Dec 2007 16:09 GMT

On Dec 14, 9:24 am, paul.robin...@it-tallaght.ie wrote:

> On Dec 14, 2:11 pm, "sugargen...@gmail.com" <sugargen...@gmail.com>
> wrote:
[quoted text clipped - 31 lines]
> regards
> Paul

thanks, Paul

Reply to this Message