Dynamic increase of rows according to the size as well format of the text

Hi,

I am making an automated excel, using excel 2003 and vba coding of excel, in which a text is copied from one cell of a sheet to the other cells of the other sheet. I have the requirement to increase the rows according to the text so that I can show all the text information without expanding the row height or width (I am taking constant width = 172 and heigth = 60). The text is of Font "Calibri" of size "40". To see it properly zoom to 23%. I am using the following code:-

Please Login or Register  to view this content.

In this code I am Spliting the text on the basis of line feeds and then calculating the no. of rows requirement of each paragraph. After that the code merges all the rows required for a paragraph and then copy the paragraph to the merged rows. The same step repeated for the next paragraph till the end of the text. But, the problem is when the text is huge and contains many paragraph and each paragraph contains many character. In this case when the rows are merged and paragraph are copied then the last few paragraphs does not show the information completely even if they contains the whole contents of the paragraph if you don't expand the heigth and width of the rows. This issue comes only in case of large paragraph and having more number of line feeds. Is there any way we can copy the text to the merge cells and showing all the text in that cell or cells without expanding the row heigth and width. Also, Concatenate and LookUp is not required because I want text to be updatable after being copied to the merged rows.

Summary of requirement:-

Need to dynamic increase of rows according to the text.
Need constant row height = 60 and width = 172 and Font "Calibri" of Size "40" of the merged rows.
After text being copied row expansion horizontally and vertically shold not be required.
The merged cells should show all the text as it is copied without hiding any content. Also, cell data should be updatable.
Any logic is welcomed if above points are being considered.
Please help!!!!!!!

Your post does not comply with the Forum Rules regarding use of code tags. All VBA code must be wrapped in code tags. Please edit your last post the add the code tags. After you have added the code tags solutions will be suggested.

Rule #3

Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button. For more information about these and other tags, click here.

Originally Posted by jitenderaswal

Thanks for suggesting. I am new to Forum . So was not aware

Try reading the Forum Rules

Using Merged Cells is a bad idea