Set a column width to same as double-clicking the separator?

AChimpNamedCornelius

Board Regular
Joined
Aug 22, 2002
Messages
91
When you double-click the column separator (same thing for rows) right after the column, the column width will be set to just wide enough to display the widest entry in that column.

I want to be able to do this programatically from VBA. I have found nothing like this in any message. Setting the column width would, presumably, be easy, just set the width of a cell in that column (is this correct?)

However, I would prefer to avoid doing some tricky thing with fonts and getting the actual cell's display width and calculating the width of the string version of the cell, etc.

The reason I want to do this is because I have a file that may be edited by many people, and I want to set it up to a standard setup from the workbook open function every time it's opened. I've already figured out how to select the proper sheet, unscroll to the top, and select a blank, innocuous cell.

Speaking of which, are there quick programatic answers to related questions:

- Get the display value of a cell vs. the formula therein

- Copy and paste a formula in relative or absolute manner
This message was edited by AChimpNamedCornelius on 2002-09-09 14:05
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
On 2002-09-09 14:03, AChimpNamedCornelius wrote:
When you double-click the column separator (same thing for rows) right after the column, the column width will be set to just wide enough to display the widest entry in that column.

I want to be able to do this programatically from VBA. I have found nothing like this in any message. Setting the column width would, presumably, be easy, just set the width of a cell in that column (is this correct?)

However, I would prefer to avoid doing some tricky thing with fonts and getting the actual cell's display width and calculating the width of the string version of the cell, etc.

The reason I want to do this is because I have a file that may be edited by many people, and I want to set it up to a standard setup from the workbook open function every time it's opened. I've already figured out how to select the proper sheet, unscroll to the top, and select a blank, innocuous cell.

Speaking of which, are there quick programatic answers to related questions:

- Get the display value of a cell vs. the formula therein

- Copy and paste a formula in relative or absolute manner
This message was edited by AChimpNamedCornelius on 2002-09-09 14:05

Wow, a lot of questions in there ! :wink:

> I want to be able to do this programatically from VBA.

Columns(1).AutoFit

> Get the display value of a cell vs. the formula therein

I'm not sure what you mean, with

Range("A1").Value

you get the value, and with

Range("A1").Formula

you get the formula, in English language.

> Copy and paste a formula in relative or absolute manner

That depends on how you set your formula in the first place, just like in Excel.
If your formula is

=$A$1

it will ALWAYS be =$A$1, no matter where you copy it. But, if you set it to

=A1

the formula will be "adjusted" depending on where you drag it, or copy it. Or, you could use something like

Range("A1").Formula = Range("A3").Formula

to copy the formula exactly how it appears in A3.
 

Forum statistics

Threads
1,144,326
Messages
5,723,724
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top