Hiding Columns AND rows based on a cell value.

[mcarterlgk]

I am building a sheet as a building configuration guide for sales staff and customers. The customer is given the option of having several buildings next to each other, each with different options

Cell B8 contains the number of side-by-side buildings the customer is asking for and is restricted to whole numbers between 1 and 10.

Each column B through K represents one building, and rows are used for the options available. In the mrexcel forums I already found and modified the necessary code to automatically hide the columns that are not in use.

I have calculated values in row 35 that the code uses to determine if the column is in use. (B35=B8-1, C35=B35-1, D35=C35-1, etc.)

Rows 64 through 97 contain options that are only applicable if the customer wants more then one building.

I would like to modify my existing column hide/unhide code so that when B8<2 Rows 64-97 are automatically hidden and when B8>1 Rows 64-97 are automatically un-hidden.

My existing code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B8")) Is Nothing Then Exit Sub
Application.EnableEvents = False 'to prevent endless loop


Dim col As Range
Dim rng As Range
Dim x As Long


Set rng = ActiveSheet.UsedRange
x = 35


For Each col In rng.Columns
y = col.Column
If Cells(x, y) < 0 And y > 2 Then col.EntireColumn.Hidden = True Else: col.EntireColumn.Hidden = False
Next col


Application.EnableEvents = True
End Sub


(Sorry, I am not sure how to make the code look as nice as some of the posts I have seen here)

Any idea on how to add this functionality or to proceed in a better way would be greatly appreciated.

 

[Roderick_E]

try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$8" Then
If Range("B8") < 2 Then
Rows("64:97").Hidden = True
End If
If Range("B8") > 1 Then
Rows("64:97").Hidden = False
End If
End If
End Sub

 

[mcarterlgk]

I got it to work.

I had to modify it a bit to work in my existing code, but that was far less difficult the I thought it would be.

Thanks, I appreciate the time.