VBA to autofit merged rows

[Johfra]

I have been searching online and I keep finding that it some cases, it works, in others it is not recommended.

I have a spreadsheet with many different information that requires specific row size but I have one row where the information could exceed its given space....where the autofit is needed.

I have tried many VBAs but none really worked. I am still new to this, though I love to learn more and eventually understand what the codes actually mean.

Am I dreaming too big or can this actually be done?

Thanks.

 

[Sky Tucker]

Johfra,

Couldn't you simply use Wrap Text/Word Wrap?

 

[Johfra]

That doesn't work. After much searching, it says I need to use VBA but its quite complex. I tried several online but none seem to work (despite the changes I needed to make).

I know about VBA but, I'm no programmer.

 

[Sky Tucker]

OK, Johfra--try this link:

http://www.ozgrid.com/forum/showthread.php?t=34947

 

[smithb2]

Johfra,

Here's the code to auto fit an entire column, in this example column A, but I don't think excel will let you autofit a merged cell, or a single cell for that matter.

Columns("A:A").EntireColumn.AutoFit


.............But, if I understand what you want to do, I think this will work. Just leave out the merging.


Note: A great way to learn code and what it does is to use the record macro feature built into excel, located under the "tools" menu.

Go to tools --> macro --> record new macro

Once you change any of the options in the resulting window, if desired, and click "OK", Excel will record every action, in VBA code, you perform, like resizing columns, until you click the stop square within the macro window that pops up when you start recording.

After you stop recording, go to tools --> macro --> visual basic editor to view the code Excel recorded and select the workbook and module of the macro you recorded.

After you feel comfortable with VBA basics, there is even an option to record with relative referencing, by reference from a particular point instead of directly.

This option is located on the macro window right next to the "stop recording" button.

Happy programming,
Smithb2