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
