How to autofit variable columns?

[Joe User]

I have a macro that fills in one-line text in a variable range from sCell to
Range(sCell).cells(nRow,nCol). When I am done, I want to autofit the
columns.

Originally, I wrote Range(sCell).Resize(nRow,nCol).Columns.Autofit.

Then I realized that this is autofitting based on only the new data. Data
in pre-existing surrounding rows might require wider columns.

If sCell were "B6" and nCol were 10, and I did this manually, I would select
columns B through K, and click on Format > Column > Autofit. Recording a
macro, that effectively does Columns("B:K").Columns.Autofit.

How can I accomplish the same thing using the variables sCell, nRow and
nCol?

I think either I want a variable Columns(...) specification that encompasses
the columns from sCell through sCell+nCol-1, or I want a variable Range(...)
or Resize(...) specification that encompasses the rows from 1 through the
last-used row of the columns from sCell through sCell+nCol-1.

At least conceptually. I think.

 

[Joe User]

If sCell were "B6" and nCol were 10, and I did this manually,
I would select columns B through K, and click on
Format > Column > Autofit. Recording a macro, that
effectively does Columns("B:K").Columns.Autofit.

How can I accomplish the same thing using the variables
sCell, nRow and nCol?

I have come up with a lame way to do it, namely:

Dim s as String
s = Range(sCell).Resize(1, nCol).Address
Columns(Mid(s, 2, 1) & ":" & Mid(s, InStr(s, ":") + 2, 1)).Columns.AutoFit

That satisfies my immediate need.

But I would still appreciate it if someone could provide a more reasonable
way of doing the same thing. I suspect there is one.


----- original message -----

 

[Joe User]

Untested, perhaps: [....]
Range(sCell).Resize(nRow,nCol).EntireColumn.Autofit

Tested. Perfetto! Grazie.

I don't know the "cost" of Resize, but FYI, the following works equally
well:

Range(sCell).Resize(1,nCol).EntireColumn.Autofit


----- original message -----

I have a macro that fills in one-line text in a variable range from
sCell to
Range(sCell).cells(nRow,nCol). When I am done, I want to autofit the
columns.

Originally, I wrote Range(sCell).Resize(nRow,nCol).Columns.Autofit.

Then I realized that this is autofitting based on only the new data.
Data
in pre-existing surrounding rows might require wider columns.

If sCell were "B6" and nCol were 10, and I did this manually, I would
select
columns B through K, and click on Format > Column > Autofit. Recording
a
macro, that effectively does Columns("B:K").Columns.Autofit.

How can I accomplish the same thing using the variables sCell, nRow
and
nCol?

I think either I want a variable Columns(...) specification that
encompasses
the columns from sCell through sCell+nCol-1, or I want a variable
Range(...)
or Resize(...) specification that encompasses the rows from 1 through
the
last-used row of the columns from sCell through sCell+nCol-1.

At least conceptually. I think.

Untested, perhaps:

Code:
--------------------
Range(sCell).Resize(nRow,nCol).EntireColumn.Autofit
--------------------

?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=171976

Microsoft Office Help