VBA to change row heights based on content of a column range

[tbablue]

Hi forum,

Apologies - I've tried searching through old posts using keywords like VBA, row height, cell value - but I'm struggling to find a previously posted solution.

I should know how to do this but I'm somewhat shame faced.



I want to change row heights based on content of a column range, BW56:BW345.

For every cell in that range I need value = "" to size row height as 20 - in all other instances, row height to be 70.

Can anyone help?

Kind regards

 

[My Aswer Is This]

Try this:
This script runs automatically when you manually change a value in the range.
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/7/2019  12:52:26 PM  EST
If Not Intersect(Target, Range("BW56:BW345")) Is Nothing Then
If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value
Case ""
    Target.RowHeight = 20
Case Else
    Target.RowHeight = 70
End Select
End If
End Sub

 

[tbablue]

Hi my friend - most grateful for your time and attention.

If Not Intersect doesn't work in this instance because the values in the range are all delivered by calculation and the column is hidden - I should have been more explicit. Apologies.

Select Case was my first thought but I'm struggling to implement it with a loop to cycle through the rows in the range. In an ideal world the macro would trigger only when anything in the range changes.

Any ideas?

 

[My Aswer Is This]

I'm not sure how to help you.
You never mentioned in your original post you wanted to run the script on a Hidden sheet.
I'm not sure if that is possible. And you did not say if the sheet is Hidden or Very Hidden.

And you said:

In an ideal world the macro would trigger only when anything in the range changes.​

So how are things changing on a Hidden Sheet?
Are they changing as a result of some formula or what?

It's always best to provide all specific details.

Like is the sheet Protected?

 

[DanteAmor]

Put the following code in a module:

Sub change_row_heights()
    Application.ScreenUpdating = False
    Range("BW56:BW345").RowHeight = 70
    For Each cell In Range("BW56:BW345")
        If cell.Value = "" Then cell.RowHeight = 20
    Next
End Sub

 

[tbablue]

TY DanteAmor

Fired by a calculation event. Most grateful. Sincerely.



@MyAnswerIsThis - I'm certain my initial description of my requirement was inadequate and I wanted to TY for your most earnest time & attention. Truly grateful.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.