Can I determine the effect of autofit to .columnwidth prior to executing the autofit?

I have a few columns where the .columnwidth property should be determined by autofit because the data is variable, but each column's width should be no more smaller or larger than a defined range of allowed widths.

At the moment, I'm doing the autofit and then testing .columnwidth afterwards, and if .columnwidth is outside the range, the code then changes .columnwidth to the minimum or maximum, whichever is closer (whichever has been exceeded).

The trouble with this approach is that .columnwidth is being changed twice per column (sometimes three times because of another part of the code) if it falls outside the range of allowed widths. It takes quite a while, roughly half a second, for the code to apply a change to .columnwidth and when this is multiplied out by the number of columns being resized, it turns an otherwise quick code into something that takes a minute or more.

Really what I want to do is workout what .columnwidth would be after autofit has been applied, but before applying it. That way I can deal with the range of allowable widths before .columnwidth is changed in the first instance.

I suspect getting some kind of size attribute for a string within a column and then somehow converting that to a measurement unit that can be compared with the value of .columnwidth. But I'm stumped as to exactly how to achieve it and would really appreciate the help of you good folk.

Many thanks

Kadeo

This is not perfect but gives you a method to get close to what you want
If you use a monospaced font like Lucida Console, then all spaces and characters take up the same amount of space and the cell containing the most characters will be the widest.

The macro below ends with a message box telling you how many characters are in the cell containing the most characters in columnA in the active sheet
Then it's "trial and error" with font sizes and number of characters to identify where to set allowable widths

NOTE - Remember to change the font (in whichever column you are aiming this macro at) to Lucida Console (or some other monospaced font)

Please Login or Register  to view this content.

Hi, thank you for the reply.

If I'm following your code right, won't this just give me the value and range of the cell that contains the most characters in the specified column (col "A")? What I'm actually looking for, I think, is the physical length of the longest text, in inches, or pixels or whatever, no matter what font it's in.

Let's say the physical length of the value of the cell with the longest amount of text in the column is less than, say, only an inch and a half long. Then when it comes time to autofit the columns I want to the code to make an executive decision and say "actually don't autofit this column because it's gonna end up an inch and a half wide which is less than the minimum two inches, so instead, make the column width two inches wide.".

I would imagine there's a way somehow, because the autofit itself has to match the column width to the length of the longest text in the column no matter what font it's in.

Hi, thank you for the reply.

If I'm following your code right, won't this just give me the value and range of the cell that contains the most characters in the specified column (col "A")? What I'm actually looking for, I think, is the physical length of the longest text, in inches, or pixels or whatever, no matter what font it's in.

Let's say the physical length of the value of the cell with the longest amount of text in the column is less than, say, only an inch and a half long. Then when it comes time to autofit the columns I want to the code to make an executive decision and say "actually don't autofit this column because it's gonna end up an inch and a half wide which is less than the minimum two inches, so instead, make the column width two inches wide.".

I would imagine there's a way somehow, because the autofit itself has to match the column width to the length of the longest text in the column no matter what font it's in.

Are you only want to fix the width if the auto-fit makes the column too narrow?
(ie you do not care if it is very wide)

I want to do both.

If I can figure out how to get a physical measurement of the length of the text and then either convert that or the columnwidth into something I can compare with the other, then I reckon I've got the rest figured out.

I do not think it is possible to set the width in inches ( and yes, it does sound unlikely!)
Column width is based on a "standard character width" - which is not helpful

So we need to find a way to convert characters to approximate width

This is what I did:

I used Lucida Console font (which is monospaced) , size 10 and used trial and error to get what I wanted

On my system 2 inches = (vba) width 23 = 21 characters

With that information
(assuming I use Lucida Font size 10)
we can tell VBA not to auto-width columns
unless
max characters in column (as determined by macro in post#02) >21

columnwidth.jpg

Thanks for your response again Kevin

Actually, I'm a bit embarrassed (Actually, I'm a lot embarrassed).

It turns out that there is a thread already on Excel Forum that has the basic idea for how I can do this. This thread in fact. Now, the keen eyed among you will notice that not only did I start that thread (that's right!) but I actually answered it as well, pretty much. So apologies for wasting anyone's time and I really do appreciate it if you have spent some on this.

Anyway, for the sake of completeness, I'll give this thread the same treatment that I gave that thread and explain my solution.

It turns out that the .width property of a range is measured in points whereas the .columnwidth property of a range is based on character size. Fortunately, the measuring units of an activex label on a worksheet (really a textbox) is also measured in points. So, like in the example in the link above ::embarrassed emoji::, I just created an actixex label, autosized it, and used the resulting width in my calculation to determine if the column width after being autofit will be outside of tolerance. This code could of course be tweaked to receive a multi-row, single column range and then cycle through the cells before returning a result or could be used to actually apply the width to the column based on it's findings and then I can forget about autofit all together.

Please Login or Register  to view this content.

Obviously, this particular function would need to be applied to every cell in the range that the autofit would be calculated against (which in the case of the actual autofit usually means every cell in the column from the top to the bottom of the worksheet) but luckily, that range is only a few rows in my data. Unfortunately, even when I base an actual autofit on that specific range instead of the whole column, it still takes a while to process.

So, while this code does answer my original post, be warned, it may not actually be any quicker without being tweaked as mentioned above.

@kadeo
Interesting approach.
When I have time I will compare your solution to mine for accuracy.

Something that does puzzle me is why (post#01) you say that some of your columns are being autosized 3 times?
Resizing once at the end would be fast
If you are stringing bits of code together and each one has an autosize, could you not prevent intermediate autosizes running?