Filter Column

[Sudipta Sen]

Hi Friends,

I want to filter data columnwise not row wise.
eg.
Stationey Purchased List

Item Jan Feb March April May June
Pen y y y y
Pencil y y y
Scale y y y
Rubber y y y y

Now I want to filter Pencil purchased in which month, means want to filter
only pencil with only purchased month, other months will be off.

First I filter Pencil then how I will get the only purchased month.

Result will be like that

Item Jan Feb April
Pencil y y y


Please help me to solve this problem.

With loves,
Sudipta

 

[Gary''s Student]

An interesting question. Try this sample:

Sub SuperFilter()
Dim r As Range
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nLastColumn = r.Columns.Count + r.Column - 1
st = Application.InputBox(prompt:="enter item: ", Type:=2)

For i = 2 To nLastRow
If Cells(i, 1).Value <> st Then
Cells(i, 1).EntireRow.Hidden = True
Else
nn = i
End If
Next

For i = 2 To nLastColumn
If Cells(nn, i).Value <> "y" Then
Cells(nn, i).EntireColumn.Hidden = True
End If
Next

End Sub

It will hide the un-wanted rows AND the un-wanted columns.