Hiding Rows and Columns

[SmartyPants]

I have two check boxes.

Depending on which is checked, I have a cell that reads either 1 or 2.

Right now, I have conditional formatting set up to "Black Out" sections
of the workbook, depending on which box is checked.

Is there any way to just hide these rows or columns?

The blacked out thing looks kinda tacky.

Example: Cell=1, then hide rows 2,3,and 4. Cell=2, then hide rows 8, 9,
and 10.

Something like that.

 

[AndrewArmstrong]

Smarty,
Try something like this, go to VBE, from the Microsoft Excel Objects
under your Workbook, select the sheet you want to work with, then paste
in this code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Left(Target.Address, 2) = "$A" Then
If Target.Value = "1" Then
Rows("2:4").EntireRow.Hidden = True
ElseIf Target.Value = "2" Then
Rows("8:10").EntireRow.Hidden = True
ElseIf Target.Value = "" Then
Cells.Select
Selection.EntireRow.Hidden = False
End If
End If
End Sub

Let me know if this is what you were looking for.
Andrew Armstrong

 

[Bob Phillips]

How are you blacking out, conditional formatting?

You could try event code, like so


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== change to suit
Const COLUMNS_1 As String = "M:O" '<=== change to suit
Const COLUMNS_2 As String = "T:Z" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Columns(COLUMNS_1).Hidden = .Value = 1
Me.Columns(COLUMNS_2).Hidden = .Value = 2
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Oops, I see you said CF. Doh!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[SmartyPants]

I did it like this


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim SHIFT As Variant

SHIFT = Sheets("Production").Range("DN28").Value

If SHIFT = 1 Then

Sheets("Production").Rows("37:38").EntireRow.Hidden = True
Sheets("Production").Rows("34:36").EntireRow.Hidden = False



ElseIf SHIFT = 2 Then

Sheets("Production").Rows("34:36").EntireRow.Hidden = True
Sheets("Production").Rows("37:38").EntireRow.Hidden = False


End If

 

[Bob Phillips]

Surely DN38 is Target, so why not just check target?

and no need to set entirerow when addressing rows.,

And you can do the hide/unhide in one statement as I showed

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$DN$38" Then
Me.Rows("37:38").Hidden = Target.Value = 1
Me.Rows("34:36").Hidden = Target.Value = 2
End If
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)