AutoFit Madness

[Josh Sale]

I'm going nuts trying to figure out what's wrong with this bit of code:

Range(r1.Offset(, bias2), r1.Offset(0, bias1 +
bias2)).EntireColumn.AutoFit

When executed no autofitting occurs. No error either. If I look at the
individual ranges they have addresses like $G$11 and $R$11 respectively.


If I change the code to:

Range(r1.Offset(, bias2).Address, r1.Offset(0, bias1 +
bias2).Address).EntireColumn.AutoFit

then it works as expected but I don't typically turn my ranges into
addresses and they've always worked OK. So why not here?


I also tried:

r1.Parent.Range(r1.Offset(, bias2), r1.Offset(0, bias1 +
bias2)).EntireColumn.AutoFit

but that didn't change anything (i.e., it still failed to autofit).


Anybody got any idea what's going on?

TIA,

josh

p.s. I'm doing this testing under XL97

 

[Jim Cone]

Hi Josh,

I assume that r1 and r2 refer to cells on a specific sheet.
Also, that the columns you are trying to autofit are on the
active sheet (which is not the sheet that tR1 and R2 refer to).

Therefore...
Range(r1, r2)).EntireColumn.AutoFit
works only on the sheet that r1/r2 refer to.

However...
Range(r1.Address, r2.address).EntireColumn.AutoFit
works only on the active sheet, as the address property
returns a String that does not include a sheet reference.

It is a good idea to include specific sheet references
in code unless you know that the code will always apply
to the active sheet.

An example (notice the dots)
With Worksheets("DataSheet")
.Range(.Range(r1.address), .Range(r2.Address)).EntireColumn.AutoFit
End with
'---------------

Regards,
Jim Cone
San Francisco, USA

 

[Josh Sale]

Hey Jim, good catch!

I had thought of what you suggested and had examined r1 and r2's parent to
make sure they were the correct worksheet ... and they seemed to be.
However, upon more careful examination, they pointed to a like named
worksheet in a different workbook.

Thanks,

josh