View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Joe User[_2_] Joe User[_2_] is offline
external usenet poster
 
Posts: 905
Default How to autofit variable columns?

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.