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

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


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"SV" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

SV wrote:
>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

--
Message posted via http://www.officekb.com

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

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"SV" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>