Filtering Columns
 

Hi MVP's/MS Geeks:
Am looking for a way out; could anyone in group bail me out. I have table
whose Column A forms a vertical heading range [A1:A10]& Row 2 forms the
horizontal heading range [B1:N1]. The data range is [B2 : N10]. I would like
to program in such way that by clicking on any cell of Vertical heading (for
e.g. A5) displays a filtered data with a criteria that only those columns
against A5(or of row 5) whose value is between 1-100 (Hides those columns
who fails to meet the criteria).

In short i need buttons on each cell of vertical heading [A2:A10] and
program each button to meet the criteria that filters/displays only those
columns whose value is between 1 & 100, hides the rest; upon selecting any of
the button

xpecting a Easter egg solution; pretty much appreciated...
thanxs...

Filtering Columns
 

On Mar 17, 5:25 pm, Mike wrote:
Hi MVP's/MS Geeks:
Am looking for a way out; could anyone in group bail me out. I have table
whose Column A forms a vertical heading range [A1:A10]& Row 2 forms the
horizontal heading range [B1:N1]. The data range is [B2 : N10]. I would like
to program in such way that by clicking on any cell of Vertical heading (for
e.g. A5) displays a filtered data with a criteria that only those columns
against A5(or of row 5) whose value is between 1-100 (Hides those columns
who fails to meet the criteria).

In short i need buttons on each cell of vertical heading [A2:A10] and
program each button to meet the criteria that filters/displays only those
columns whose value is between 1 & 100, hides the rest; upon selecting any of
the button

xpecting a Easter egg solution; pretty much appreciated...
thanxs...

Try putting this in the sheet's code:
'------------------
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

'Declarations
Dim strActiveColumn As String
Dim strDummy As String
Dim strActiveRow As String
Dim iCounter As Integer

'Unhide all columns
For iCounter = 2 To 14
Columns(iCounter).Hidden = False
Next iCounter

'Make sure we're in column 'A'
strActiveColumn = Mid(ActiveCell.Address, 2, 1)

If strActiveColumn = "A" Then
strDummy = ActiveCell.Address

'Find out what row we're in
Do Until Right(strDummy, 1) = "$"
strActiveRow = strActiveRow & Right(strDummy, 1)
strDummy = Left(strDummy, Len(strDummy) - 1)
Loop

'Test each column in the row for a value = 100, if less, hide.
For iCounter = 2 To 14
If Cells(strActiveRow, iCounter) < 100 Then
Cells(strActiveRow, iCounter).EntireColumn.Hidden =
True
End If
Next iCounter
End If
End Sub


Double click on any entry in column A to "filter" and double click
anywhere else to undo the filtering .... this is pretty neat!

Chris

Filtering Columns
 

On Mar 17, 8:12 pm, cht13er wrote:
On Mar 17, 5:25 pm, Mike wrote:



Hi MVP's/MS Geeks:
Am looking for a way out; could anyone in group bail me out. I have table
whose Column A forms a vertical heading range [A1:A10]& Row 2 forms the
horizontal heading range [B1:N1]. The data range is [B2 : N10]. I would like
to program in such way that by clicking on any cell of Vertical heading (for
e.g. A5) displays a filtered data with a criteria that only those columns
against A5(or of row 5) whose value is between 1-100 (Hides those columns
who fails to meet the criteria).

In short i need buttons on each cell of vertical heading [A2:A10] and
program each button to meet the criteria that filters/displays only those
columns whose value is between 1 & 100, hides the rest; upon selecting any of
the button

xpecting a Easter egg solution; pretty much appreciated...
thanxs...

Try putting this in the sheet's code:
'------------------
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

'Declarations
Dim strActiveColumn As String
Dim strDummy As String
Dim strActiveRow As String
Dim iCounter As Integer

'Unhide all columns
For iCounter = 2 To 14
Columns(iCounter).Hidden = False
Next iCounter

'Make sure we're in column 'A'
strActiveColumn = Mid(ActiveCell.Address, 2, 1)

If strActiveColumn = "A" Then
strDummy = ActiveCell.Address

'Find out what row we're in
Do Until Right(strDummy, 1) = "$"
strActiveRow = strActiveRow & Right(strDummy, 1)
strDummy = Left(strDummy, Len(strDummy) - 1)
Loop

'Test each column in the row for a value = 100, if less, hide.
For iCounter = 2 To 14
If Cells(strActiveRow, iCounter) < 100 Then
Cells(strActiveRow, iCounter).EntireColumn.Hidden =
True
End If
Next iCounter
End If
End Sub

Double click on any entry in column A to "filter" and double click
anywhere else to undo the filtering .... this is pretty neat!

Chris

ps. If you want to be able to "filter" by more than just "100" (e.g.
filter by <200, or =75) (user enters the desired filter onto the
sheet) I have some code for that - just let me know!

C

Filtering Columns
 

Hi Chris,
Hope u had a great weekend!!!

Thanks a bunch for your solution; solved my purpose except for this loop:
'Find out what row we're in
Do Until Right(strDummy, 1) = "$"
strActiveRow = strActiveRow & Right(strDummy, 1)
strDummy = Left(strDummy, Len(strDummy) - 1)
Loop

When i double click on row 1; code does filter the columns and displays
correct results...and same results untill row 9. From row 10 it fails to
filter; for my curiosity i found that row 11 and multiples of 11 like (row
22, 33, 44, 55....) does give correct filtered result. Could you check this
loop. Thanks.

regards,
Mike.

"cht13er" wrote:

On Mar 17, 5:25 pm, Mike wrote:
Hi MVP's/MS Geeks:
Am looking for a way out; could anyone in group bail me out. I have table
whose Column A forms a vertical heading range [A1:A10]& Row 2 forms the
horizontal heading range [B1:N1]. The data range is [B2 : N10]. I would like
to program in such way that by clicking on any cell of Vertical heading (for
e.g. A5) displays a filtered data with a criteria that only those columns
against A5(or of row 5) whose value is between 1-100 (Hides those columns
who fails to meet the criteria).

In short i need buttons on each cell of vertical heading [A2:A10] and
program each button to meet the criteria that filters/displays only those
columns whose value is between 1 & 100, hides the rest; upon selecting any of
the button

xpecting a Easter egg solution; pretty much appreciated...
thanxs...

Try putting this in the sheet's code:
'------------------
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

'Declarations
Dim strActiveColumn As String
Dim strDummy As String
Dim strActiveRow As String
Dim iCounter As Integer

'Unhide all columns
For iCounter = 2 To 14
Columns(iCounter).Hidden = False
Next iCounter

'Make sure we're in column 'A'
strActiveColumn = Mid(ActiveCell.Address, 2, 1)

If strActiveColumn = "A" Then
strDummy = ActiveCell.Address

'Find out what row we're in
Do Until Right(strDummy, 1) = "$"
strActiveRow = strActiveRow & Right(strDummy, 1)
strDummy = Left(strDummy, Len(strDummy) - 1)
Loop

'Test each column in the row for a value = 100, if less, hide.
For iCounter = 2 To 14
If Cells(strActiveRow, iCounter) < 100 Then
Cells(strActiveRow, iCounter).EntireColumn.Hidden =
True
End If
Next iCounter
End If
End Sub


Double click on any entry in column A to "filter" and double click
anywhere else to undo the filtering .... this is pretty neat!

Chris

Filtering Columns
 

On Mar 25, 4:50*am, Mike wrote:
Hi Chris,
Hope u had a great weekend!!!

Thanks a bunch for your solution; solved my purpose except for this loop:

* * * * 'Find out what row we're in
* * * * Do Until Right(strDummy, 1) = "$"
* * * * * * strActiveRow = strActiveRow & Right(strDummy, 1)
* * * * * * strDummy = Left(strDummy, Len(strDummy) - 1)
* * * * Loop

When i double click on row 1; code does filter the columns and displays
correct results...and same results untill row 9. From row 10 it fails to
filter; for my curiosity i found that row 11 and multiples of 11 like (row
22, 33, 44, 55....) does give correct filtered result. Could you check this
loop. Thanks.

regards,
Mike.



"cht13er" wrote:
On Mar 17, 5:25 pm, Mike wrote:
Hi MVP's/MS Geeks:
Am looking for a way out; could anyone in group bail me out. I have table
whose Column A forms a vertical heading range [A1:A10]& Row 2 forms the
horizontal heading range [B1:N1]. The data range is [B2 : N10]. I would like
to program in such way that by clicking on any cell of Vertical heading (for
e.g. A5) displays a filtered data with a criteria that only those columns
against A5(or of row 5) *whose value is between 1-100 (Hides those columns
who fails to meet the criteria).

In short i need buttons on each cell of vertical heading [A2:A10] and
program each button to meet the criteria that filters/displays only those
columns whose value is between 1 & 100, hides the rest; upon selecting any of
the button

xpecting a Easter egg solution; pretty much appreciated...
thanxs...

Try putting this in the sheet's code:
'------------------
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

'Declarations
* * Dim strActiveColumn As String
* * Dim strDummy As String
* * Dim strActiveRow As String
* * Dim iCounter As Integer

'Unhide all columns
* * For iCounter = 2 To 14
* * * * Columns(iCounter).Hidden = False
* * Next iCounter

'Make sure we're in column 'A'
* * strActiveColumn = Mid(ActiveCell.Address, 2, 1)

* * If strActiveColumn = "A" Then
* * * * strDummy = ActiveCell.Address

* * * * 'Find out what row we're in
* * * * Do Until Right(strDummy, 1) = "$"
* * * * * * strActiveRow = strActiveRow & Right(strDummy, 1)
* * * * * * strDummy = Left(strDummy, Len(strDummy) - 1)
* * * * Loop

* * 'Test each column in the row for a value = 100, if less, hide..
* * * * For iCounter = 2 To 14
* * * * * * If Cells(strActiveRow, iCounter) < 100 Then
* * * * * * * * Cells(strActiveRow, iCounter).EntireColumn.Hidden =
True
* * * * * * End If
* * * * Next iCounter
* * End If
End Sub

Double click on any entry in column A to "filter" and double click
anywhere else to undo the filtering .... this is pretty neat!

Chris- Hide quoted text -

- Show quoted text -

HAHA

Blame that one on the "$" key just beside my arrow buttons on my
laptop!

"$" should be "" ... just delete the $ sign.

Tell me if that works!

Chris

Filtering Columns
 

Hi Chris,

It's not the poor '$' the culprit; I debugged the code and found the absence
of this statement: 'strActiveRow = StrReverse(strActiveRow)' should place
after Loop:-
Do Until Right(strDummy, 1) = "$"
strActiveRow = strActiveRow & Right(strDummy, 1)
strDummy = Left(strDummy, Len(strDummy) - 1)
Loop

so that the strActiveRow would hold for example row 15 as it was holding 51
before instead.

Now it works perfect....thanks again.

1:- I am trying to place buttons in Column A instead of double clicking on;
for example cell 'A5' will have button and filtering will done upon clicking
on the button.

2:- I would like to place the number of coloumn dynamic type; instead of
'14' in the following code snippet
'Unhide all columns
For iCounter = 2 To 14

code should first read the number of coloumns of the sheet that holds the
data and place in a variable(ColCount) then
For iCounter = 2 To ColCount

3:- I would like to hide all the rows which is not Activerow for filtering.
Only the row which was doubleclicked for filtering and displaying the
filtered data should be shown.

well i guess; that it for now...if you have solution for it...could you
please share it...
thanks.
regards,
Mike-

- Show quoted text -

HAHA

Blame that one on the "$" key just beside my arrow buttons on my
laptop!

"$" should be "" ... just delete the $ sign.

Tell me if that works!

Chris

Filtering Columns
 

Just wanna share the solution of my following request#2 in my previous post:-
2:- I would like to place the number of coloumn dynamic type; instead of
'14' in the following code snippet
'Unhide all columns
For iCounter = 2 To 14

ColCount = Selection.CurrentRegion.Columns.Count
'
'
For iCounter = 2 To ColCount
'
'
now am working on request# 1 & 3....

Filtering Columns
 

On Mar 26, 5:14 pm, Mike wrote:
Hi Chris,

It's not the poor '$' the culprit; I debugged the code and found the absence
of this statement: 'strActiveRow = StrReverse(strActiveRow)' should place
after Loop:-
Do Until Right(strDummy, 1) = "$"
strActiveRow = strActiveRow & Right(strDummy, 1)
strDummy = Left(strDummy, Len(strDummy) - 1)
Loop

so that the strActiveRow would hold for example row 15 as it was holding 51
before instead.

Now it works perfect....thanks again.

1:- I am trying to place buttons in Column A instead of double clicking on;
for example cell 'A5' will have button and filtering will done upon clicking
on the button.

2:- I would like to place the number of coloumn dynamic type; instead of
'14' in the following code snippet
'Unhide all columns
For iCounter = 2 To 14

code should first read the number of coloumns of the sheet that holds the
data and place in a variable(ColCount) then

For iCounter = 2 To ColCount

3:- I would like to hide all the rows which is not Activerow for filtering.
Only the row which was doubleclicked for filtering and displaying the
filtered data should be shown.

well i guess; that it for now...if you have solution for it...could you
please share it...
thanks.
regards,
Mike-

- Show quoted text -

HAHA

Blame that one on the "$" key just beside my arrow buttons on my
laptop!

"$" should be "" ... just delete the $ sign.

Tell me if that works!

Chris

All right, glad you fixed that one little problem!

Here are some ideas for the next three points:

1) You could very easily change the code to accomplish this, like so:
insert a commandbutton using the "forms" toolbar into each row in
column a. Have them going to their own macro, which gives it a number
according to the row it's in, and then call on the macro you're using
now (after you call it something new e.g. "Public Sub FilterMe()" in a
new module, not in the worksheet) with that row # to get strActiveRow.
I don't know if it's worth all the trouble doing it this way - maybe
someone else can tell us if there's a way to get code to run when you
select a cell??

2) Easy - insert this somewhere...it will go along the cells in row 1
one by one until it finds a blank

cells(1,2).select
colcount=1
do until activecell=""
colcount=colcount+1
activecell.offset(0,1).select
loop

3) Add another loop immediately before "Select Case strOperator"

'hide all rows except ours
For iCounter = 2 To 7
If iCounter < strActiveRow Then
Rows(iCounter).Hidden = True
End If
Next iCounter


and add this right after you unhide all columns at the beginning of
the code:

'Unhide all rows
For iCounter = 2 To 7
Rows(iCounter).Hidden = False
Next iCounter

You can also add a loop to get RowCount and do the same thing ....

4) One more thing - change "strActiveColumn = Mid(ActiveCell.Address,
2, 1)" to "strActiveColumn = Mid(Target.Address, 2, 1)" ... and change
"strDummy = ActiveCell.Address" to "strDummy = Target.Address"

If you email me I will email you the file I have so you can get
code...

Cheers

Chris

Filtering Columns
 

On Mar 26, 6:58 pm, cht13er wrote:
On Mar 26, 5:14 pm, Mike wrote:



Hi Chris,

It's not the poor '$' the culprit; I debugged the code and found the absence
of this statement: 'strActiveRow = StrReverse(strActiveRow)' should place
after Loop:-
Do Until Right(strDummy, 1) = "$"
strActiveRow = strActiveRow & Right(strDummy, 1)
strDummy = Left(strDummy, Len(strDummy) - 1)
Loop

so that the strActiveRow would hold for example row 15 as it was holding 51
before instead.

Now it works perfect....thanks again.

1:- I am trying to place buttons in Column A instead of double clicking on;
for example cell 'A5' will have button and filtering will done upon clicking
on the button.

2:- I would like to place the number of coloumn dynamic type; instead of
'14' in the following code snippet
'Unhide all columns
For iCounter = 2 To 14

code should first read the number of coloumns of the sheet that holds the
data and place in a variable(ColCount) then

For iCounter = 2 To ColCount

3:- I would like to hide all the rows which is not Activerow for filtering.
Only the row which was doubleclicked for filtering and displaying the
filtered data should be shown.

well i guess; that it for now...if you have solution for it...could you
please share it...
thanks.
regards,
Mike-

- Show quoted text -

HAHA

Blame that one on the "$" key just beside my arrow buttons on my
laptop!

"$" should be "" ... just delete the $ sign.

Tell me if that works!

Chris

All right, glad you fixed that one little problem!

Here are some ideas for the next three points:

1) You could very easily change the code to accomplish this, like so:
insert a commandbutton using the "forms" toolbar into each row in
column a. Have them going to their own macro, which gives it a number
according to the row it's in, and then call on the macro you're using
now (after you call it something new e.g. "Public Sub FilterMe()" in a
new module, not in the worksheet) with that row # to get strActiveRow.
I don't know if it's worth all the trouble doing it this way - maybe
someone else can tell us if there's a way to get code to run when you
select a cell??

2) Easy - insert this somewhere...it will go along the cells in row 1
one by one until it finds a blank

cells(1,2).select
colcount=1
do until activecell=""
colcount=colcount+1
activecell.offset(0,1).select
loop

3) Add another loop immediately before "Select Case strOperator"

'hide all rows except ours
For iCounter = 2 To 7
If iCounter < strActiveRow Then
Rows(iCounter).Hidden = True
End If
Next iCounter

and add this right after you unhide all columns at the beginning of
the code:

'Unhide all rows
For iCounter = 2 To 7
Rows(iCounter).Hidden = False
Next iCounter

You can also add a loop to get RowCount and do the same thing ....

4) One more thing - change "strActiveColumn = Mid(ActiveCell.Address,
2, 1)" to "strActiveColumn = Mid(Target.Address, 2, 1)" ... and change
"strDummy = ActiveCell.Address" to "strDummy = Target.Address"

If you email me I will email you the file I have so you can get
code...

Cheers

Chris

I have a "filter value" in cell A11 .. e.g "12" or "33" ... and this
code goes under the sheet:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

'Declarations
Dim strActiveColumn As String
Dim strDummy As String
Dim strActiveRow As String
Dim iCounter As Integer
Dim strOperator As String
Dim varFilter As Variant
Dim ColCount As Integer
Dim RowCount As Integer

'Count columns
Cells(1, 2).Select
ColCount = 1
Do Until ActiveCell = ""
ColCount = ColCount + 1
ActiveCell.Offset(0, 1).Select
Loop

'Count rows
Cells(2, 1).Select
RowCount = 1
Do Until ActiveCell = ""
RowCount = RowCount + 1
ActiveCell.Offset(1, 0).Select
Loop

'Unhide all columns
For iCounter = 2 To ColCount
Columns(iCounter).Hidden = False
Next iCounter

'Unhide all rows
For iCounter = 2 To RowCount
Rows(iCounter).Hidden = False
Next iCounter

'Make sure we're in column 'A'
strActiveColumn = Mid(Target.Address, 2, 1)

If strActiveColumn = "A" Then
strDummy = Target.Address

'Find out what row we're in
Do Until Right(strDummy, 1) = "$"
strActiveRow = strActiveRow & Right(strDummy, 1)
strDummy = Left(strDummy, Len(strDummy) - 1)
Loop

strActiveRow = StrReverse(strActiveRow) ' new

'If row is between 2 and RowCount, go ahead!
If strActiveRow 1 And strActiveRow <= RowCount Then

varFilter = Cells(11, 1)
If IsNumeric(varFilter) = True Then
strOperator = 1
ElseIf Left(varFilter, 1) = "" Then
strOperator = 2
varFilter = Mid(varFilter, 2, Len(varFilter) - 1)
ElseIf Left(varFilter, 1) = "<" Then
strOperator = 3
varFilter = Mid(varFilter, 2, Len(varFilter) - 1)
Else
Call MsgBox("Bad filter", vbOKOnly, "Error")
Exit Sub
End If

'hide all rows except ours
For iCounter = 2 To RowCount
If iCounter < strActiveRow Then
Rows(iCounter).Hidden = True
End If
Next iCounter


Select Case strOperator
'Test each column in the row against operator and filter
Case 1:
For iCounter = 2 To ColCount
If Cells(strActiveRow, iCounter) < varFilter Then
Cells(strActiveRow,
iCounter).EntireColumn.Hidden = True
End If
Next iCounter
Case 2:
For iCounter = 2 To ColCount
If Cells(strActiveRow, iCounter) < CSng(varFilter)
Then
Cells(strActiveRow,
iCounter).EntireColumn.Hidden = True
End If
Next iCounter
Case 3:
For iCounter = 2 To ColCount
If Cells(strActiveRow, iCounter) CSng(varFilter)
Then
Cells(strActiveRow,
iCounter).EntireColumn.Hidden = True
End If
Next iCounter
End Select
End If
End If
End Sub