Here we go again....

[ExcelBob]

Cell A1 contains the heading MONTH:

Cells A3:A41 contain the word "April"
Cells A42:A80 contain the word "May"
Cells A81:A119 contain the word "June"

etc all the way to March.

I want to put a pick list in cell A2 with the months April thru to
March and when you select a month it only returns the rows containing
that month.
ie if you pick April from the list it only displays rows 3:41
if you pick May from the list it displays rows 42:80 etc etc

Can anyone tell me how I can do this?

Help greatly appreciated

 

[Guest]

Data > Filter > AutoFilter > click the arrow on column A and select "April"

When finished reviewing, do Data > Filter > AutoFilter again and it toggles
everything back to normal......

Vaya con Dios,
Chuck, CABGx3

 

[Arvi Laanemets]

Hi

Why not use autofilter (from Data>Filter) - it does exactly what you want
(and even more)

 

[Pete_UK]

Why can't you use Autofilter for this? - Higlight column A then Data |
Filter | Autofilter (tick). You will have a pull-down arrow in A1 which
will present you with the list of available values in column A - no
need for A2 (you can delete this row if that is all you used it for).

Hope this helps.

Pete

 

[Arvi Laanemets]

Hi

Data > Filter > AutoFilter > click the arrow on column A and select
"April"

When finished reviewing, do Data > Filter > AutoFilter again and it
toggles
everything back to normal......

No need to remove autofilter. Simply click on arrow in Month column header
again, and select "Show All".

 

[Guest]

Hi Arvi........
Actually, I just offered that as one way to get out of the
AutoFilter......in my own programs, I usually put a toggle-pushbutton above
the Freeze line and just use it to go into and out of the AutoFilter.

Vaya con Dios,
Chuck, CABGx3

 

[ExcelBob]

I want the default month to be April and all other months to be hidde
until selected from the list. I don't want the user to be able t
'show all' and I want the list to be in Month order as opposed t
alphabetcal order.

That's why Autofilter is not good enough.

I know it's more straightforward just to use Autofilter and be happ
with it but I would prefer it this way if anyone can work out how i
can be done!


Cheers Everyon

 

[ExcelBob]

so basically i want

all rows hidden (except rows 1 & 2)
default in a2 to be "april" (so it doesn't allow a blank)

if cell a2 = "april" show rows 3-41
if cell a2 = "may" show rows 42-80
if cell a2 = "june" show rows 81-11

etc

any magicians out there?

 

[Guest]

Running short on time here, but this code,(based on some supplied by Jim
Tomlinson) should help you get started.........

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target.Value = "april" Then Call April
If Target.Value = "may" Then Call May
If Target.Value = "june" Then Call June
End If
End Sub

Sub April()
Rows("2:112").Select
Selection.EntireRow.Hidden = False
Rows("42:80").Select
Selection.EntireRow.Hidden = True
Rows("81:111").Select
Selection.EntireRow.Hidden = True
Range("A2").Select
End Sub

Sub May()
Rows("2:112").Select
Selection.EntireRow.Hidden = False
Rows("3:41").Select
Selection.EntireRow.Hidden = True
Rows("81:111").Select
Selection.EntireRow.Hidden = True
Range("A2").Select
End Sub

Sub June()
Rows("2:112").Select
Selection.EntireRow.Hidden = False
Rows("3:41").Select
Selection.EntireRow.Hidden = True
Rows("42:80").Select
Selection.EntireRow.Hidden = True
Range("A2").Select
End Sub


hth
Vaya con Dios,
Chuck, CABGx3

 

[ExcelBob]

CLR

QUALITY!!! Just what I was after!

Many Many Thanks

 

[Guest]

Happy to help.....thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3