VBA code to automatically adjust row height based on formula output

[povictory]

Hello, wondering if anyone here can help with this issue I'm having -

I have a workbook with 3 tabs that may or may not have text in cell A1. I also have a summary tab in this workbook where I am wanting to concatenate all of the text from the other 3 tabs, on different lines, in A1 of the summary tab *if* text exists. If one or more of the 3 source tabs are showing nothing in cell A1, I would like that data to be ignored in my concatenated summary tab in A1. Additionally, I would like the summary tab A1 row height to automatically increase and decrease to fit the number of lines with data.

To help illustrate, let's say I have the following data in each tab:

'Tab1'!A1 = Text1
'Tab2'!A1 = Text2
'Tab3'!A1 = Text3

In the 'SummaryTab'A1, I want to have a formula and/or VBA code that would pull in the info from the other 3 tabs and expand the row height to accommodate the 3 lines, assuming A1 is populated on each tab:

'Text1
Text2
Text3'

However, if I then went into 'Tab2'!A1 and deleted 'Text2', I would like A1 on the summary tab to update as shown below, and resize to 2 lines instead of 3:

'Text1
Text3'

Hope that makes sense. I have been playing around with this and I am stumped. Not sure if it can be done the way I'm thinking but thought I would throw it out just in case anyone might have any ideas. Any assistance is greatly appreciated!

 

[Zot]

This is straight forward easy to follow code. Modify to your need.

On each worksheet module, put this code

Private Sub Worksheet_Change(ByVal Target As Range)
Call Summarize
End Sub

In standard module, put this code

Sub Summarize()

Dim Result As String
Dim wsTab1 As Worksheet, wsTab2 As Worksheet, wsTab3 As Worksheet, wsSummary As Worksheet
Dim wb As Workbook

Application.ScreenUpdating = False

Set wb = ActiveWorkbook
Set wsTab1 = wb.Sheets("Tab1")
Set wsTab2 = wb.Sheets("Tab2")
Set wsTab3 = wb.Sheets("Tab3")
Set wsSummary = wb.Sheets("Summary Tab")

wsSummary.Range("A1").Delete

If Not wsTab1.Range("A1") = "" Then Result = wsTab1.Range("A1")
If Not wsTab2.Range("A1") = "" Then Result = Result & vbLf & wsTab2.Range("A1")
If Not wsTab3.Range("A1") = "" Then Result = Result & vbLf & wsTab3.Range("A1")

wsSummary.Range("A1") = Result
If Left(wsSummary.Range("A1"), 1) = Chr(10) Then
    wsSummary.Range("A1") = Right(wsSummary.Range("A1"), Len(wsSummary.Range("A1")) - 1)
End If

End Sub

 

[povictory]

Very helpful...thank you!