Prev: Object Heirarchy Chart
Next: VBA forms in excel
From: Sudipta Sen on 27 Feb 2010 04:06
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

From: Gary''s Student on 27 Feb 2010 07:26
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.
--
Gary''s Student - gsnu201001


"Sudipta Sen" wrote:

> 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
>
 | 
Pages: 1
Prev: Object Heirarchy Chart
Next: VBA forms in excel