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

[kashif.special2005]

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)

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