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