Hello all,

I'm looking for a way to filter data when a person enters a date in a
given cell.
Actually it doesn't need to be a cell, if it can be done with an
inputbox, that's fine.

In the example below a date is entered in A1, that date turns out to
be a saturday.
Now I want the macro to filter te collumn SAT.


25/04/09 SATURDAY

MON TUE WED THU FRI SAT SUN TEST

X X X X X X X Y
X X X X X X X Y
X X X X N
X X X X X N
X X X X X X X Y
X X X X X X X N

I hope someone can help me on this one
Regards,
M!ke

Hi Mucah!t

I've recently had this problem myself as I've wanted to filter horizontally.
I've ended up adapting a macro (sorry the post I found never mentioned the
original auther) to hide all columns in a range, and only unhide one(s) where
a cell in the header row matched the contents of my input cell:

the input cell is B3 and the column ranges are E:V, with the headers on row
5 (starting at E5). If the word "All" was entered in B3, all columns were
shown.
The macro stops looping when it comes across a column with ""(blank) in row 5

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
If Range("B3").Value = "All" Then
Range("E1:V1").EntireColumn.Hidden = False
Else
Range("E1:V1").EntireColumn.Hidden = True
Range("E5").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = Range("B3").Value Then
Selection.EntireColumn.Hidden = False
End If
ActiveCell.Offset(ColumnOffset:=1).Activate
Loop
End If
Range("B3").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End If
End Sub

This is worksheet code, but can easily be adaped to a module
Hope this helps

"Mucah!t" wrote:

Hello all,

I'm looking for a way to filter data when a person enters a date in a
given cell.
Actually it doesn't need to be a cell, if it can be done with an
inputbox, that's fine.

In the example below a date is entered in A1, that date turns out to
be a saturday.
Now I want the macro to filter te collumn SAT.


25/04/09 SATURDAY

MON TUE WED THU FRI SAT SUN TEST

X X X X X X X Y
X X X X X X X Y
X X X X N
X X X X X N
X X X X X X X Y
X X X X X X X N

I hope someone can help me on this one
Regards,
M!ke

On Apr 24, 9:10*am, Bryce wrote:
Hi Mucah!t

I've recently had this problem myself as I've wanted to filter horizontally.
*I've ended up adapting a macro (sorry the post I found never mentioned the
original auther) to hide all columns in a range, and only unhide one(s) where
a cell in the header row matched the contents of my input cell:

the input cell is B3 and the column ranges are E:V, with the headers on row
5 (starting at E5). *If the word "All" was entered in B3, all columns were
shown.
The macro stops looping when it comes across a column with ""(blank) in row 5

Private Sub Worksheet_Change(ByVal Target As Range)

* * If Target.Address = "$B$3" Then
* * * * Application.EnableEvents = False
* * * * Application.ScreenUpdating = False
* * * * * * If Range("B3").Value = "All" Then
* * * * * * * * Range("E1:V1").EntireColumn.Hidden = False
* * * * * * Else
* * * * * * * * Range("E1:V1").EntireColumn.Hidden = True
* * * * * * * * Range("E5").Select
* * * * * * * * Do Until ActiveCell.Value = ""
* * * * * * * * * * If ActiveCell.Value = Range("B3").Value Then
* * * * * * * * * * * * Selection.EntireColumn.Hidden = False
* * * * * * * * * * End If
* * * * * * * * ActiveCell.Offset(ColumnOffset:=1).Activate
* * * * * * Loop
* * * * * * End If
* * * * Range("B3").Select
* * * * Application.ScreenUpdating = True
* * * * Application.EnableEvents = True
* * End If
End Sub

This is worksheet code, but can easily be adaped to a module
Hope this helps



"Mucah!t" wrote:
Hello all,

I'm looking for a way to filter data when a person enters a date in a
given cell.
Actually it doesn't need to be a cell, if it can be done with an
inputbox, that's fine.

In the example below a date is entered in A1, that date turns out to
be a saturday.
Now I want the macro to filter te collumn SAT.

25/04/09 SATURDAY

MON * * * *TUE * * WED * * THU * * FRI * * SAT * * SUN * * TEST

X *X * * * X * * * X * * * X * * * X * * * X * * * Y
X *X * * * X * * * X * * * X * * * X * * * X * * * Y
* *X * * * X * * * * * * * X * * * X * * * * * * * N
* *X * * * X * * * X * * * X * * * X * * * * * * * N
X *X * * * X * * * X * * * X * * * X * * * X * * * Y
X *X * * * X * * * X * * * X * * * X * * * X * * * N

I hope someone can help me on this one
Regards,
M!ke- Hide quoted text -

- Show quoted text -

Thanks for your help Bryce,
However I still find it hard to adapt your macro to my situation.
Guess my knowledge about VBA isn't sufficient yet.

It is possible that Bryce has mis-interpreted your problem.

Do you want to hide the columns so that only the SAT column remains
visible (as I think Bryce is assuming) or do you want to filter the
SAT column to show only the 'Y's or 'N's?

If the latter is the case you could do worse than recording a macro as
you perform the filter you want and then pick through the code and
come back here with a more specific question if you get stuck.

Br, Nick H

Yes, I see I mis-interpreted the problem.

A quick solution, using xl 2007, I've had a play, and with a lookup table
giving days and the equivelant field to filter:

Mon 1
Tue 2
Wed 3
Thu 4
Fri 5
Sat 6
Sun 7

with A1 being the input date, in B1 I have the following formula
=VLOOKUP(TEXT(A1,"ddd"),Days,2,0)
with Days being the name of the above lookup

The formula in B2 should give the result of the column you wish to filter,
then use it as a reference in the macro

the macro is :
ActiveSheet.Range("$A$5:$G$17").AutoFilter Field:=Range("B1"), Criteria1:="y"

Adapt the range and criteria to suit