Excel VBA Count Number Of Lines From A Merge Cells Range And Wrap Text

kashif.special2005

Active Member
Joined
Oct 26, 2009
Messages
443
Hi,

I have very unique problem here and from past 2 days I searched the answer in google or asked with some other VAB expert but I could not get a answer, please kindly help me to resolve this problem.

Problem:-

I have multiple footnotes (footnote cells C21, C44, C120, C178 etc.,) in a excel template, and one thing is common is that all footnotes comments are 5 row and 7 column merge with wraptext.

footnotes C21 range will be :- C21:I25, merged with wraptext
footnotes C44 range will be :- C44:I48, merge with wraptext

etc.,


User is entering the text in the cell with alt + Enter or just typing the text (I don't know user can enter the text as per their requirements), if user just typing the text it will automatically get in new line if the text is reached max length in the merge cells.

Now my task is that if the entered text in footnotes cells (C21, C44, C120, C178 etc.,) is clearly visible within the merged 5 rows then do nothing.

But if the entered text in the footnotes cells (C21, C44, C120, C178 etc., ) is not clearly visible then insert the required rows so that entered footnotes clearly visible to the respective managers when they received the user's filled templates.

I have no idea how can I achieve this, however I written down some code below which is working ok if the user entered large footnotes which is not clearly visible within the 5 rows, but I am stuck where the footnotes is clearly visible the below code then it should not create a row, but it is creating.

Is there any way we can read the lines, that mean how may lines in a merge wrapt text (including alt + enter and just normal text)


Code:
Private Sub Expand_Footnotes_Rows(template)




    Dim footnotes As Variant, vTemp As Variant
    Dim iLoop As Integer, iLoop2 As Integer, iCount As Integer
    Dim ws As Worksheet
    
    
    Set ws = template.Worksheets("Call Template")
    
    
    footnotes = Array(21, 44, 83, 144, 161, 181, 198, 362, 393, 463, 511)
    
    
    For iLoop = LBound(footnotes) To UBound(footnotes)
        ws.Cells(footnotes(iLoop), "Z").Value = "Footnotes"
    Next iLoop
    
    
    For iLoop = 21 To 1000
        
        If ws.Cells(iLoop, "Z").Value = "Footnotes" Then
            iCount = 0
            vTemp = Split(ws.Cells(iLoop, "C").Value, Chr(10))
    
            
            For iLoop2 = LBound(vTemp) To UBound(vTemp)
                iCount = iCount + Int(Len(vTemp(iLoop2)) / 100)
            Next iLoop2
            
            
            If iCount > 0 Then
                ws.Range(ws.Cells(iLoop + 4, "A"), _
                        ws.Cells(iLoop + 4 + (UBound(vTemp) + iCount - 4), "A")).EntireRow.Insert
            
            ElseIf UBound(vTemp) > 0 Then
                ws.Range(ws.Cells(iLoop + 4, "A"), _
                        ws.Cells(iLoop + 4 + UBound(vTemp) - 4, "A")).EntireRow.Insert
            
            End If


        End If
    Next iLoop
    
    ws.Range(ws.Cells(1, "Z"), ws.Cells(1000, "Z")).ClearContents
    
End Sub

Kindly request to all VBA expert, please please help me to correct my code.

Thanks in advance

Kashif
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top