Conditional Hiding of Rows

[SV]

Hello,

I am new with VB macro, so far I have created an option button with
the objective to hide tons of rows but when i started entering the row
numbers it became painful, i even thot of named ranges but that would
be an issue if i added or deleted a single row from my worksheet!! so
far my code goes like this:

Dim nAmHide As Boolean

Private Sub nAmhide1_Click()
nAmHide = False
End Sub

Private Sub nAmhide2_Click()
nAmHide = True
End Sub

For Each sht In Sheets

If nAmHide = True And sht.Name = "Assumptions" Then
sht.Rows("4:5").EntireRow.Hidden = True
sht.Rows("15:16").EntireRow.Hidden = True
sht.Rows("24:25").EntireRow.Hidden = True
sht.Rows("33:34").EntireRow.Hidden = True
sht.Rows("43:44").EntireRow.Hidden = True
End If

in the above code rows 4:5 represents country France for example,
15:16 is germany, 24,:25 is Spain, 33:34 is Italy and so on the
country names comes in repititions uptil rows 1300. I thot of namesd
ranges but that will be a pain!

Is there a way to conditonally say if its France then hide those rows
in that sheet, or if its UK , then hide those rows specifically and
so on and so forth??

Thanks much!
SV

 

[Don Guillett]

You should be able to do this easily with a SELECT CASE macro. Look in the
vba help index

Sub hiderowsif()
Rows.Hidden = False
Select Case UCase(InputBox("enter country"))
Case Is = "USA": x = "15:20"
Case Is = "FRANCE": x = "25:30"
'etc
Case Else
MsgBox "non"
End Select
Rows(x).Hidden = True
End Sub

 

[SV]

Hi Don, thanks for posting this,
but I'd rather have to search for the country names and hide those
rows automatically rather than having to mention the row names cos
they can go all the way upto 1300.

Something that can find France or Uk and without mentioning the rows
and hide those entire rows automatically.

Thanks again!
SV

 

[Francois via OfficeKB.com]

Hi Don, thanks for posting this,
but I'd rather have to search for the country names and hide those
rows automatically rather than having to mention the row names cos
they can go all the way upto 1300.

Something that can find France or Uk and without mentioning the rows
and hide those entire rows automatically.

Thanks again!
SV

Could you not add a 'helper' column that will signify the country

eg If Country = France then cell = FR
If Country = UK then cell = UK

etc

Then do an Autofilter on your chosen selection

Wrap this up in a macro

 

[Don Guillett]

Sub findclump()
what = "F"
Rows.Hidden = False
lr = Cells(Rows.Count, "a").End(xlUp).Row
x = Columns(1).Find(what).Row 'Address
MsgBox x
y = Columns(1).Find(what, after:=Cells(lr, 1), _
SearchDirection:=xlPrevious).Row
MsgBox y
Rows(x & ":" & y).Hidden = True
End Sub