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

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

VBA Code:
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:
VBA Code:
Range(Target.Address)
"Target" is already a range variable. So you can replace what you have above with just:
VBA Code:
Target
 
Upvote 0
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
 
Upvote 0
So, you would just want to put a Worksheet_Change event procedure on your "list" sheet that looks like this:
VBA Code:
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
 
Upvote 0
Solution
You are literally the best. That's been so helpful, and I really appreciate it
 
Upvote 0
You are welcome.
Glad I was able to help!
:)
 
Upvote 0
So, you would just want to put a Worksheet_Change event procedure on your "list" sheet that looks like this:
VBA Code:
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
 
Upvote 0
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.
 
Upvote 1

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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