Autofit width of mergerd cells

Have you seen this? :

http://www.vbaexpress.com/kb/getarticle.php?kb_id=40

Dave

Just perfect! Thanks to you both for this great and fast help. I thought it's fair to split the points because both improoved the solution of each other.

Thanks from both of me :)

Dave

*lol* (sorry my fault)

Is there a possibility to adapt this to get it working from a specific column? Like let columns A-C as there are and autofit all others?

Due some reasons (I can't find out) doesn't work your VBA script on the attached sheet. I can't find the reason. Most of the merged fields are fitted perfectly, but on some it doesn't work. Is there some protection or something like this?
Testfile.xlsm

Appears to get better results when you do the mergedcell operation last, versus doing before fitting all other cells.  At least the results vary between what we were getting and something that looks good.  I put this in as a popup option when you do the operation (e.g., make a quick link for the macro on your Excel Ribbon).  That way, you can vary how it operates depending on output.  If it always works well one way, you could code that out, later.

Dave
Testfile-AutoFit-Column-Merged-v.xlsm

Thanks a lot. The late version works perfectly. It's really kind of you that you replied even after finishing this question.

Because I call the module not from a existing VBA function, there would be only one wish left: How do I have to change your (perfect) script that it does it for every column except the columns A-C?

I've some basic VBA knowledge, but I'm running against a wall trying to modify your script to ignore columns A-C.

It would be really kind if you can fullfill this last wish too. If you want I can open a linked question and give you 500 points again...

Best regards and big thanks,
abfinfo

This should get you there.  Its not perfect but about all I can spend time on unless you have a gotcha.  

Try it and let me know.

Dave
AutoFit-Column-Merged-v2.xlsm

Did that work?

Dave

This worked perfectly :) Thanks a lit dlmile and sorry for my late answer...

My joy was to early...

Everything seems to be perfect, but there is a small problem (which I spend over 4 hours now with): If there is data entered with more than two diggits.. It resizes the column not right and displays '##" (when I use late). If I use early this works great but then the headers are the problem (resized it to small).

If you've got a solution for this problem, it would be really really nice, and I promise to answer very quickly.

Best regards and thanks for all of your work,
abfinfo

Send me a sample spreadsheet, with both sides of your story.  Will take a look - will try, today.

Dave

Hi Dave,

Thanks a lot for your answer. I've attached two files. One for early, one for late. It would be really nice if there is a solution. Big thanks forwards.

Best regards,
abfinfo
AutoFit-Column-Merged-v2-1-early.xlsm
AutoFit-Column-Merged-v2-1-late.xlsm

Ok - the problem is with numeric not with text.  Should be a simple repair...

Dave

Well - it was a bit more trouble than I thought.  I needed to take the merged cell out, then add up the autofit widths of each column remaining.  If those widths were > than the merged cell width, nothing to do, but if not, THEN distribute out the columns from there.

Believe it will work with >= 2 column merges and variations (tested only briefly, beyond initial dataset - but definately with variations on entries in each column).

See attached,

Dave
AutoFit-Column-Merged-v31.xlsm