An issue with the object model?

[Bengt]

I wanted to perform a selection of some columns on a worksheet and then do some other things with that selection. The worksheet in question isn't active.
To select the columns, I did the following:

  Sheets("ACertainSheet").Activate
    Columns("H:K").Select

This worked well, but I didn't really want to make the sheet active if I could avoid it, so I tried this

  Sheets("ACertainSheet").Columns("H:K").Select

It didn't work. I am sure that you object model gurus out there can see immediately what is wrong (or perhaps you have to have the sheet active in order to select columns). So, how to solve the problem. Grateful for any help.

Thanks

Bengt

 

[VBA Geek]

I wanted to perform a selection of some columns on a worksheet and then do some other things with that selection.

in VBA you don't need to select ranges in order to work with them

 

[Michael M]

What do you actually want to do with the columns ??
The sheet must be active to select a range / columns / rows

 

[Bengt]

in VBA you don't need to select ranges in order to work with them

Thanks, but if the columns that I want to work with is on another sheet than the one that is currently active, I guess I must be able to point to the columns to tell VBA what area I want to work with. If I want to do stuff with a set of columns, how do I specify on what sheet these columns are?

Bengt

 

[Bengt]

What do you actually want to do with the columns ??
The sheet must be active to select a range / columns / rows

I was just going to do an Autofit after having written values to them, but your second sentence actually gave me the information I needed, that the sheet must be active in order for me to select the columns. Thanks veru much for this insight.

Bengt

 

[Michael M]

you could use...

    Sheets("ACertainSheet").Columns("H:K").EntireColumn.AutoFit

 

[Bengt]

you could use...

    Sheets("ACertainSheet").Columns("H:K").EntireColumn.AutoFit

Yes, that would probably be exactly what I needed. Thanks very much.

Bengt

 

[RoryA]

The key point is you don't need to select them at all:

Sheets("ACertainSheet").Columns("H:K").Autofit

for example, rather than:

Sheets("ACertainSheet").Activate
Columns("H:K").Select
Selection.EntireColumn.Autofit

 

[Michael M]

...
one step ahead of you Rory...for a change...

 

[RoryA]

(right, more coffee... )