Dynamically adjusting ColumnWidth; only increases- not decreases, based on title Row Width

[Dominic Baruzzini]

Forgive me if this isn't considered dynamic... it's dynamic...right?

I couldn't find anything online or in a book that does specifically, what this does. This macro sets a standard column width (for each column) to its current unique setting and not a value- Then compares it to the Autofit column width of the newly pasted in data. If the new data auto fit value is less than the current column width value, then the column will NOT get smaller. The column can only increase in width so that when data upload is complete, the sheet can be read intelligibly.

I'm open to any comments or feedback on the process I've chosen. I'm sure there's a more elegant method out there- I'm open to all suggestions. I've considered many different ways of tackling this problem but this one seemed to make the most sense to me.

Hope this helps someone out there.
Thank you!

Please Login or Register  to view this content.

[shg]

Welcome to the board.

You want to make all of the columns the width of the widest autofit column?

Please Login or Register  to view this content.

[Dominic Baruzzini]

Thanks for the quick reply and the welcoming response! Glad to be hear- I love this forum!

To answer your question simply; No. For clarity, please let me explain further. I'm happy with my code; it works beautifully. It's just that I'm in the process of trying to think about writing code more efficiently. That's the second reason why I posted it; the first being that maybe it could help someone with the same problem.

What I did first was manually fit all the columns to the width that I wanted; each a unique width based on the data/title(text) in my heading row... Row 11. The code above is just a small macro in a larger code that I'm calling after I link a number of workbooks and draw data from each to the rows below it. Sometimes that data is small and when pasted and auto-fitted, reduces the column width to where the heading titles become unintelligible. I didn't want that to happen. So I had to create a macro that would only allow the column width to increase and not decrease after that data was pasted in. What I came up with is above.

Have I answered your question?

Just wanted you to know I have so much respect for all you guru's and experts out there. I've seen in a short time how much work it takes to become good at this practice (of writing code). I have a long way to go to reach my 10,000 hours; on my way though.

Thanks in advance for any feedback!
Dom

[Dominic Baruzzini]

For more clarification: Each column will behave differently based on the data I paste into each column. Some may increase; some may stay the same.

[Dominic Baruzzini]

Thanks for the simple code... exactly what I was looking for. I'll play around with it and see how it works.

[shg]

Then I would code it like this, to avoid selecting:

Please Login or Register  to view this content.

[Dominic Baruzzini]

Thanks for that beauty above; very elegant. Once again, I'll play around and test it a bit. I was trying to implement the UsedRange property but couldn't figure how to use it properly. Now you've shown me.

Thanks a million. -Dom

[shg]

You're welcome.