VBA to AutoFit Row Height and AutoFit Column Width

[dslhs]

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.

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.

 

[Joe4]

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:

Range("W7:AE42").Rows.AutoFit        
Range("W7:AE42").Columns.AutoFit

should actually be written like this:
This part:

Rows("7:42").EntireRow.AutoFit        
Columns("W:AE").EntireColumn.AutoFit

 

[dslhs]

Thank you for your response.

I tried:

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?

 

[Joe4]

Thank you for your response.

I tried:

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.

 

[dslhs]

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

 

[Joe4]

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.

 

[dslhs]

I'm really sorry - I understood about 50% of that! Could you let me know what VBA I should use please?

 

[Alex Blakenburg]

should actually be written like this:
This part:

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 ?

 

[dslhs]

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?

 

[Alex Blakenburg]

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.