Autofit Column Width

[plans]

Hello,
I have range called MyRange which has its all cells lined up in a column; the cells are not sequential (they are like: A1, A5, A8, A11…).
I am trying to “Autofit Column Width” for the longest cell content in MyRange.
The standard “Autofit Column Width” is not good in my case because if in A2 (which is not part of "MyRange") I have a longer string than the max in "MyRange", than fits the column by A2 which is not what I want.
What code would do this?
Thank you

[gmr4evr1]

Try this, change the cell range as needed for MyRange

Please Login or Register  to view this content.

[Doc.AElstein]

Try this, change the cell range as needed for MyRange

Please Login or Register  to view this content.

maybe I missed a subtle point, sorry if that is the case..

...should it not be of this form...

Please Login or Register  to view this content.

Alan

[gmr4evr1]

Doc,
Normally yes you would need to enable the events at the end. I was using it for my own use and cannot for the life of me remember why I took that line out.
I do know that the code will run with or without that line. In fact, the OP could probably (I think) remove or comment out both the EnableEvents lines and it would still be ok.

[Doc.AElstein]

.....
Normally yes you would need to enable the events at the end. I was using it for my own use and cannot for the life of me remember why I took that line out.
I do know that the code will run with or without that line. In fact, the OP could probably (I think) remove or comment out both the EnableEvents lines and it would still be ok.

Ok, I did not try , I guess, yes, without my two EnableEvent lines it would do the auto fit once, causing the Workbook_SheetChange to kick in again, but then it would on the next go not actually change the width so then it would not kick off again...

--- .. but you may actually have a typo......? .. you did this
Application.ScreenUpdating = False

. so you turned the screen Updating off, and so the OP will have a dead screeen after that code runs... ( I think ? )
Alan

[gmr4evr1]

I remember why I didn't include the = True part - it's because I was running it with some other code and the = True was at the end of the code and I forgot to add it back in when I posted here. You are right though, the norm would be to set EnableEvents back to True at the end, I just forgot to add it back in there.
Strangely, if you use code I posted in a workbook module, it kicks in again, either way, for each cell after you press enter, tab or click in another cell. Even with EnableEvents set to false or with it being deleted or commented out, it still works.

[Doc.AElstein]

I remember why I didn't include the = True part - it's because I was running it with some other code and the = True was at the end of the code .....

Agree ( I think ) with all that ( Either you do not turn it off at all or if you turn it off , I think you should turn it back on, just in case you need it on again, ), -

. the main thing that concerned me was just the
Application.ScreenUpdating = False
which i expect was a typo..

[gmr4evr1]

Doc,
I just noticed that you are talking about one thing and I was talking about another...sorry about that. I'm not sure if the Enable Events is needed in this case. Would it be better for me to add it in there? Another forum moderator had looked at the code from a previous post and added it to the "Tips" forum for me and didn't say anything about it either way.
I have added the = True part into my previously posted code, just in case.

[Doc.AElstein]

.. not sure if the Enable Events is needed in this case. Would it be better for me to add it in there? ...

. As i said, I expect without the two EnableEvents Lines the code probably just kick in twice, - the second time not doing anything as the Width was already adjusted the first time. As it does nothing the second time it does not set off the code any more times. But I think it is bad practice not too put those lines in when in such a code where interact with the sheet... . In other situations it might end up in an infinite loop ( But, don’t ask me , I am no Pro!!! )

.. BUT you are using Application.ScreenUpdating - that is usually used to speed up things as it stops the whole spreadsheet updating evrytime something is done to the sheet. That is only useful ( I think ? ) when in a code things are continually being put in or taken out of the sheet. So only typically useful in things that Loop a lot, for example, which at each loop do something to the sheet.

. In the case of what the OP is doing it probably is not particularly relevant to do anything with Application.ScreenUpdating as the OP is probably not doing something Thousands of times – I mean he is not capable of typing in something Thousands of time in a second or so... So the speed improvement from Turning the screen Updating off would be lost on him.
Alan

[gmr4evr1]

I get what you are saying now (I think). Since this is not a loop, Application.ScreenUpdating is pretty much worthless and not needed. Now, when this code is placed in a workbook and/or worksheet change module it kicks off every time a cell is changed within the specified range and enter or tab is pressed or when another cell is selected. Even if a cell has already adjusted the width to the text in the cell, if the text is changed and becomes a smaller or longer string, the cell will auto adjust the width again. In this case, is the Application.ScreenUpdating still useless and not needed?
BTW, I am not pro with VBA either, still a rookie, as is evident by my rookie mistake(s) and question(s) at times.

[Doc.AElstein]

..... In this case, is the Application.ScreenUpdating still useless and not needed?.....

. I think so. It only makes a noticeable difference, as far as I am aware, if you do something that tries to change the screen lots of times. ( I mean 100 – 1000 times per second or so ). With Application.ScreenUpdating set to = True, the code would have to wait each time for the screen to update ( It updates every cell I think, even if not every cell was changed, every time anything is changed !? ). Usually then you have at the start of your code

Sub Testie()
On Error Goto TheEnd
Application.ScreenUpdating = False



.................



.......................
And then at the end of your code

The End:
Application.ScreenUpdating = True
End Sub

.... It is important to include that Error handler. That way if your code crashes it still Turns screen updating on before the program stops. If you don’t do that and the code crashes your screen will be “dead” ! , if you see what i mean. – It won’t update, that is to say change when you does anything!!! ( That is why I was originally concerned that you had Application.ScreeenUpdating = False , but no corresponding Application.ScreenUpdating = True anywhere that i could see. - Some people would do that to play a trick on someone!! )

.......................

. I rarely use the pair of lines myself, I am an old Basic man, - I try to do as much as i can "internally” with VBA Arrays. That is to say as much as possible I..
. 1 ) At the start of any code "capture” as much of the screen data i can in one go , sticking it in a large Input Array..
. 2) Do as much as possible with VBA maths and produce from all that one main Output Array...
. 3 ) Finally i Paste that one Output Array out to the Spreadsheet in one go
.
. So I hardly ever need Applicaioin,ScreenUpdating = False to speed things up - I only change the screen a couple of times, if i can help it.
Alan

[gmr4evr1]

Thank you for the very useful info about screen updating. I will look at my code and see where I can take it out. I do know that some things my code is doing required me to use screen updating true/false. But based on our discussion here, I now know it is not needed with every code.

Thank you for the Error Goto as well, as there have been many, many times where I have "hung" excel and had to force close it because of something I did in the code.

As for the Array part of your post, yea, I have no idea how to use an array in code, nor would I know when to use an array and when not to. Actually, this goes for using formulas in excel too.