Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering Columns | Excel Discussion (Misc queries) | |||
Filtering Multiple Columns | Excel Worksheet Functions | |||
Filtering columns within VB | Excel Programming | |||
Filtering by criteria in two columns | Excel Worksheet Functions | |||
Filtering by Columns | Excel Worksheet Functions |