Hide rows - VB

[Batshon]

Hi,
Am new to Vb so bare with me..

i have a cell that contains a drop down text (Yes) or (Blank)
If that cell is (Yes), row 28 to 38 should get hidden.

can anyone figure out what am suppose to write in VB? or any other formula?
thanks!!

 

[Nick Hodge]

You'd need to use a Worksheet_Change event.

I've used A1 as the cell that changes. (You need to right click on the
worksheet select view code... and paste this in the window that appears and
close the window)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Reset
With Application
.EnableEvents = False
If Not Application.Intersect(Me.Range("A1"), Target) Is Nothing Then
Select Case UCase(Target.Value)
Case Is = "YES"
Me.Rows("28:38").Hidden = True
Case Else
Me.Rows("28:38").Hidden = False
End Select
End If
.EnableEvents = True
End With
Exit Sub

Reset:
Application.EnableEvents = True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Rick Rothstein \(MVP - VB\)]

Just as a point of interest, you can replace this part of your code...

Select Case UCase(Target.Value)
Case Is = "YES"
Me.Rows("28:38").Hidden = True
Case Else
Me.Rows("28:38").Hidden = False
End Select

with this single statement....

Me.Rows("28:38").Hidden = (UCase(Target.Value) = "YES")

Rick