Automatically Auto-fit Row Height of a range of cells when any cell is updated (dynamic content)

[flatterno]

I have some VBA code which I have in the worksheet code area and I think I am close to getting it to work but not sure what I am doing wrong.

The worksheet in question is never directly edited, but a range of cells D5:D30 pull their data from cells within other sheets (using ='sheet'!H1etc). By the nature of the content these cells are going to fill up quickly so I would like each row to auto-fit height based on the content on column D. Several people will be opening this document and won't know how to do this manually and as I am worried they may miss details that go beyond the size of the cell, I'd like this process to happen automatically whenever any of the content of column D is updated.

The code I have is

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an Action when they are changed.
    Set KeyCells = Range("D5:D30")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Change the height of the header row when one of the defined cdlls is changed
        Rows("1:30").EntireRow.AutoFit

    End If
End Sub

Any one able to point out what I might be doing wrong? Do I perhaps need to attached the VBA on to the worksheet I will be editing directly?

Thanks

 

[Joe4]

Worksheet_Change event procedures only fire when data is manually updated directly on that sheet.
If the data changes on the sheet are the results of links or formulas, the Worksheet_Change event procedure will NOT fire.

Basically, you have two options:
1. If the updates are the results of formulas that pull data from another tab, if the data from that "other" tab is updated manually, then create a Worksheet_Change event procedure that fires when that tab is updated, and just have it update the other sheet.
2. Instead of using a Worksheet_Change event procedure, use a Worksheet_Calculate event procedure. This fires whenever a calculation is done on the sheet. However, note that you CANNOT identify which cell is being recalculated, so you cannot narrow it down. It will run whenever any cell anywehere on the entire sheet is recalculated. So, it could slow things down a bit, as this code may run a lot more than you actually want or need it to (cannot really limit it).

One other thing. Note that this reference is redundant and totally unnecessary:

Range(Target.Address)

"Target" is already a range variable. So you can replace what you have above with just:

Target

 

[flatterno]

that's really helpful thank you - still learning a lot here.

So if I was to say the range of editable cells on the worksheet named 'list' is D92:EC92 effectively I want to add a worksheet change event which then triggers my rows D5:D30 in worksheet 'results'. I'm not even sure how to google doing this. Perhaps I've tried to do too much here.

Thanks for your suggestion

 

[Joe4]

So, you would just want to put a Worksheet_Change event procedure on your "list" sheet that looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("D92:EC92"), Target) Is Nothing Then
        Sheets("results").Rows("1:30").EntireRow.AutoFit
    End If
    
End Sub

 

[flatterno]

You are literally the best. That's been so helpful, and I really appreciate it

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[jimmytiler]

So, you would just want to put a Worksheet_Change event procedure on your "list" sheet that looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("D92:EC92"), Target) Is Nothing Then
        Sheets("results").Rows("1:30").EntireRow.AutoFit
    End If
   
End Sub

Hi Joe4.

Hoping you can help. When you state in your code Sheets("results")... what is the "results" actually referring to; I thought the results is the EntireRow.AutoFit?

Regards,
jimmy

 

[Joe4]

Hi Joe4.

Hoping you can help. When you state in your code Sheets("results")... what is the "results" actually referring to; I thought the results is the EntireRow.AutoFit?

Regards,
jimmy

"results" is simply the name of the sheet. Since they are dealing with two different sheets in their example, they need to specify which sheet they are referring to.