Widen (#####) columns with code (Excel 2000 >)

Topic

New Reply

I would like to automate the process of widening columns that are too narrow (#####). If column is too narrow (#####), autofit it, BUT no other column widths must be changed in any way. How can I check if a column is too narrow for any data, and how do I automate this?

Reply | Quote

Replies

At it’s simplest

Selection.EntireColumn.AutoFit

however, that will make columns with a just a few characters narrower.

Reply | Quote

Hi John,

The tricky part is Selection!!! Its easy to say Entirecolumn.Autofit, but this must only apply to columns that have #### in them! No other column widths must be adjusted. If my sheet has 100 columns and 73 of those columns have #### due to being too narrow to show the number, it must widen by use of autofit. How do I determine what column has ####?
Tx

Reply | Quote

You could loop through the columns and
– Store the current width in a variable.
– AutoFit the column (look up AutoFit in the VBA help)
– Compare the new width to original width (the value of the variable).
– If the new width is LESS than the original width, restore the original width.
(If the new width is MORE, the column needed to be widened, and if it is the same, nothing has happened)

Reply | Quote

OK…this is a “lateral thinking” solution. I will attempt this code-wise. If I have any hassles with it I’ll post back with what I have built!
Tx

Reply | Quote

(Edited by JohnBF on 22-Jun-05 08:14. Code now accounts for Hans point that the original code would reset any custom column width settings, code fixes and improvements.)

I locked my machine up, TWICE, writing this, hence the delay. Never enter “?activesheet.columns” in the Immediates window. See if it’s what you want.

Sub AutofitMin()
Dim dblW As Double
Dim rngR As Range

For Each rngR In Selection.Columns
With rngR.EntireColumn
dblW = .ColumnWidth
.AutoFit
If .ColumnWidth < dblW Then .ColumnWidth = dblW
End With
Next rngR
End Sub

Reply | Quote

I would store the width for each individual column in intW within the loop.

Reply | Quote

I was trying the get the default sheet column width into intW, which I then in turn compare to each columnwidth after it is set by Autofit. I think I’m missing your point, Hans.

Reply | Quote

What if Rudi has manually changed the widths of some columns the way he wants them? Your code runs the risk of resetting them to the default column width. My idea was to compare the autofitted width of each column to the width of that column before autofit, and to restore the old width if the column has become narrower.

Reply | Quote

Ah! Of course you are correct.

Reply | Quote

Thanx Hans for the suggestions!
Thanx John for the effort and the code!

It is perfect. BIG

Reply | Quote

Hi John,

I see you used integer to store the initial column width. I’ve always thought integer only allows whole numbers. The excel help files say: stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767. Does this number reperesent decimals or is it a whole number? Also column widths are decimaled eg. 10.75. Please shed some light on what integer stores, and if column widths are stored as decimal..or is it points (whole numbers!)

TIA

Reply | Quote

You are right, make it a Double!

Reply | Quote

I’ll have a Double too, please!

Reply | Quote

Coming up – That will be $12.75 !
Cheers

Reply | Quote

If you want to check cell B1 to see if it is displaying #####, you can do the following:

If Left(Range(“B1”).Text,1)) = “#” Then

To check and AutoFit all of the cells in the current selection, you could do this:

Dim oCell As Range
For Each oCell In Selection
If Left(Trim(oCell.Text), 1) = “#” Then
oCell.EntireColumn.AutoFit
End If
Next oCell

Reply | Quote

Thanx for the suggestion Legare. I tested your code and it works just as well.

One question: Your method tests for each cell in the selection. (The selection might at certain times be up to 20 – 30 columns.) This may account for the loop running 1966080 times. When a column is autofitted, does it also test each cell internally in code? I suppose my question boils down to: Which code example is more efficient, or are they both ultimately the same? I ask simply for interest sake; I’m grateful to both you and John for helping out with the code! Many many thanx!

Reply | Quote

AutoFit works on a column as a whole, not on a single cell in a column, so it is more efficient to loop through the columns than to loop through all cells.

Reply | Quote

Thanx. You don’t need to answer this…but it makes me wonder what the code looks like that drives autofit? How is it possible to autofit a column based on the longest entry in a cell in the column without testing the iduvidual length of each cell. It must have to do with arrays… or your favourite UBound/LBound functions…

Reply | Quote

Of course the internal code used by Excel looks at each individual cell, but you as a programmer need not be concerned by that – for you, AutoFill operates on an entire column (or entire row).

Reply | Quote

I haven’t seen MS’ code, so I can’t say for sure, but my guess would be that it is going to loop through all of the cells to find out which has the longest entry.

Reply | Quote

Yes, I guess this will be the case. As Hans commented earlier, this will be seen as one “event” for us using VBA in excel. Its all compiled into one method that does one action!
Tx for the extra code though!
Cheers

Reply | Quote

Again, I can’t be positive, but it might be more efficient to loop through all the cells than it is to autofit the column width for columns that don’t need it and then change the column width back.

Reply | Quote

If the range has many columns and few rows, your code might be slightly more efficient, but if it contains many rows, looping through all cells is costly – the code has to check them all, even though most columns will already have been autofitted early on. Since a worksheet has 256 columns and 65,536 rows, looping through columns will in general be faster, I think.

Reply | Quote

But the AutoFit is also going to have to loop through all the cells. I have also posted a modification to eliminate checking unnecessary rows when entire columns are selected.

Reply | Quote

That raises a question. I have always assumed that Excel’s ‘internal’ methods operate faster than equivalent VBA code, because they are coded in C# and compiled rather than runtime. So when in doubt I use the closest existing method rather than reproducing it. What do you think?

Reply | Quote

> I have always assumed that Excel’s ‘internal’ methods operate faster than equivalent VBA code
That was the reason for my remark.

Reply | Quote

See my last post.

Reply | Quote

Doing the equivalent thing in VBA and internal code, then I would think that the internal code is always more efficient. However, in this case, I don’t know that the loops are equivalent. I have no idea what the internal code is doing in its loop. It may be having to calculate the width of the contents of each cell, which may require a call to the display driver for each cell. That could be much more inefficient than VBA to look to see if the first character is a “#”. Since the font may or may not be a porportional font, you can’t just look at the length of the displayed characters to know what the widest width required is.

Reply | Quote

I would guess C++ rather than C#…

Reply | Quote

To me, it’s

Reply | Quote

Surely that should be

Reply | Quote

Quiz

Reply | Quote

por supuesto.

Reply | Quote

Tip (and a bit off subject – but it may be helpful): For any English language only Loungers, try copying and pasting unknown words into the following site, selecting (guessing) the language and click on Translate.

http://babelfish.altavista.com/%5B/url%5D

Leigh

Reply | Quote

I also just thought that if you are going to be selecting entire columns, then my code could be made more efficient by this change:

Dim oCell As Range
For Each oCell In Intersect(Selection, ActiveSheet.UsedRange)
If Left(Trim(oCell.Text), 1) = “#” Then
oCell.EntireColumn.AutoFit
End If
Next oCell

Reply | Quote

You could also try:

Sub FindSmallColumns()
   Dim rngFound As Range
   With Selection
      Set rngFound = .Find(what:="#", LookIn:=xlValues, lookat:=xlPart, MatchCase:=True)
      If Not rngFound Is Nothing Then
         Do
            rngFound.EntireColumn.AutoFit
            Set rngFound = .FindNext(rngFound)
         Loop While Not rngFound Is Nothing
      End If
   End With
End Sub

which works for me in XL2002. The downside is it will refit any columns with cells containing # in them (e.g. “invoice#”)

Reply | Quote

Thanx Rory. Your answer is just as valid.
Tx

John. I see you reworked your code in post 490558. Thanx for your input!

Reply | Quote

Hi

I am pretty new with Excel so please ignore if this does’nt help but I downloaded the ASAP free add in utilities and one of the options seems to do what you require

Best regards

Danny

Reply | Quote

Thanx for your input Danny. I do know about this utility, and I have used it in the past! The underlying reason for my original post is to learn to code this. I’m playing a lot with VBA for Excel currently and I am trying to break out of a “way of thinking” that I have developed. I find the lounge as a good resource to get different ideas and code structures. It is helping me to think out of the box and be more creative in developing solutions to common issues in Excel. Simply put…the lounge is a BIG learning curve for me!

My deepest thanx and respect to the lounge and all who contribute to my development!

Reply | Quote