VBA to AutoFit Row Height and AutoFit Column Width

dslhs

New Member
Joined
Apr 4, 2022
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I'm pretty sure there is a simple solution to this, but I'm struggling.

I would like a VBA code that is triggered by a change to the contents of the cell A2.

When A2 is changed, it will automatically AutoFit Row Height and AutoFit Column Width for the cells W7:AE42.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)    
' Check if the changed cell is B2
If Target.Address = "$B$2" Then        
Application.EnableEvents = False                '
Range("W7:AE42").Rows.AutoFit        
Range("W7:AE42").Columns.AutoFit                
' Re-enable events        
Application.EnableEvents = True    
End IfEnd Sub

I thought this would work, but alas it hasn't.

Any help would be much appreciated.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
When A2 is changed, it will automatically AutoFit Row Height and AutoFit Column Width for the cells W7:AE42.

Do you have Autowrap turned on for those cells?

Do you have some carriage returns in any of those cells?

This part:
VBA Code:
Range("W7:AE42").Rows.AutoFit        
Range("W7:AE42").Columns.AutoFit
should actually be written like this:
This part:
VBA Code:
Rows("7:42").EntireRow.AutoFit        
Columns("W:AE").EntireColumn.AutoFit
 
Upvote 0
Thank you for your response.

I tried:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Application.EnableEvents = False                '
Rows("7:42").EntireRow.AutoFit
Columns("W:AE").EntireColumn.AutoFit
Application.EnableEvents = True
End If
End Sub

But it didn't work unfortunately. Is there anything else I could try?
 
Upvote 0
Thank you for your response.

I tried:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Application.EnableEvents = False                '
Rows("7:42").EntireRow.AutoFit
Columns("W:AE").EntireColumn.AutoFit
Application.EnableEvents = True
End If
End Sub

But it didn't work unfortunately. Is there anything else I could try?
Please answer the questions I asked in the previous post and show us a sample of your data and expected results.
 
Upvote 0
Thanks - Autowrap is on. It's all VLOOKUP data, so I won't be able to share it.

I've made a script within Excel:

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Auto fit the rows of range W7:AE42 on selectedSheet
selectedSheet.getRange("W7:AE42").getFormat().autofitRows();
// Auto fit the columns of range W7:AE100 on selectedSheet
selectedSheet.getRange("W7:AE42").getFormat().autofitColumns();
}

That works - however, it is only triggered by a button and I'd like it to be triggered by a change to A2
 
Upvote 0
Worksheet_Change is only triggered by manual changes to cells. So if the data in cell A2 is the result of a formula, you cannot use Worksheet_Change, unless you are watching the input cells the formula in cell A2 is referencing (and those cells are manually updated).

There is a Worksheet_Calculate event that fires whenever any cell in the entire sheet is re-calculated. The caveat to that is it cannot watch a particular cell (or range of cells). All it can detect is that SOME cell SOMEWHERE on the sheet was recalculated. So it will run whenever that happens anywhere on the sheet.
 
Upvote 0
I'm really sorry - I understood about 50% of that! Could you let me know what VBA I should use please?
 
Upvote 0
should actually be written like this:
This part:
VBA Code:
Rows("7:42").EntireRow.AutoFit
Columns("W:AE").EntireColumn.AutoFit
That is not entirely correct. It depends on what you want to achieve.
The original: Range("W7:AE42").Columns.AutoFit will autofit column W:AE to the widest data in rows 7:42. Possibly unlikely given the columns used but if there was say a report title in W1 which was much wider than the data under it in rows 7:42, you wouldn't want it autofit to the report title size which entirecolumn would do while the selected rows version would not.
For rows as far as I can tell both methods produce the same result.

@dslhs, you keep mentioning A2 but your change event is hard coded to only run the autofit code when B2 changes, would changing the B2 to A2 in the code fix the issue ?
If not, the cell that needs to drive the autofit, is it manually changed or does it contain a formula ?
 
Upvote 0
Good spot. Unfortunately, when I change it to the correct cell A2 neither VBA works (but the script triggered by a button does).

Does it matter that the A2 cell is a drop-down? Would that cause issues?
 
Upvote 0
Do you mean a validation drop down box or Form control or Active X box ?
A validation drop down should be fine the other 2 not so much.

Make your selected cell A2 and show us a picture of the screen including the formula bar, the excel column and row grid references.
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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