Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
Hi All.....
I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
You can modify this instruction to be run on one or multiple columns; as is,
it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
Thanks Michael, but I guess I wasn't clear in my post. I only want my macro
to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
OK, modify my sub and make sure you also copy the function.
Function courtesy of http://www.ozgrid.com/VBA/autofilter-criteria.htm Sub IsFilteron() Dim iLastRow As Double Dim numCells As Double iLastRow = Range("B65536").End(xlUp).Row numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count If numCells < iLastRow Then ' This determines if there is a filter on B strFltrCrit = AutoFilter_Criteria(Range("B1")) 'This returns the criteria used on the filter MsgBox (strFltrCrit) 'Call yourmacro End If End Sub Function AutoFilter_Criteria(Header As Range) As String Dim strCri1 As String, strCri2 As String Application.Volatile With Header.Parent.AutoFilter With .Filters(Header.Column - .Range.Column + 1) If Not .On Then Exit Function strCri1 = .Criteria1 If .Operator = xlAnd Then strCri2 = " AND " & .Criteria2 ElseIf .Operator = xlOr Then strCri2 = " OR " & .Criteria2 End If End With End With AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2 End Function -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Thanks Michael, but I guess I wasn't clear in my post. I only want my macro to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
Another way (anywhere in col-b)
Sub test() MsgBox ColFilterOn(ActiveSheet, "B") MsgBox ColFilterOn(ActiveSheet, 2) End Sub Function ColFilterOn(ws As Worksheet, col) As Boolean Dim af As AutoFilter If VarType(col) = vbString Then col = ws.Range(col & 1).Column End If On Error GoTo errExit Set af = ws.AutoFilter On Error GoTo 0 If Not af Is Nothing Then ColFilterOn = Not Intersect(Columns(col), af.Range) Is Nothing End If errExit: End Function Regards, Peter T "CLR" wrote in message ... Thanks Michael, but I guess I wasn't clear in my post. I only want my macro to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
Almost there Michael...........
This one will give a "pass" if ANY column is filtered on, and will return a blank window if the column is not "B". Somehow if we could take that return and allow a "pass" only if the return was NOT blank. Vaya con Dios, Chuck, CABGx3 "Michael" wrote: OK, modify my sub and make sure you also copy the function. Function courtesy of http://www.ozgrid.com/VBA/autofilter-criteria.htm Sub IsFilteron() Dim iLastRow As Double Dim numCells As Double iLastRow = Range("B65536").End(xlUp).Row numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count If numCells < iLastRow Then ' This determines if there is a filter on B strFltrCrit = AutoFilter_Criteria(Range("B1")) 'This returns the criteria used on the filter MsgBox (strFltrCrit) 'Call yourmacro End If End Sub Function AutoFilter_Criteria(Header As Range) As String Dim strCri1 As String, strCri2 As String Application.Volatile With Header.Parent.AutoFilter With .Filters(Header.Column - .Range.Column + 1) If Not .On Then Exit Function strCri1 = .Criteria1 If .Operator = xlAnd Then strCri2 = " AND " & .Criteria2 ElseIf .Operator = xlOr Then strCri2 = " OR " & .Criteria2 End If End With End With AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2 End Function -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Thanks Michael, but I guess I wasn't clear in my post. I only want my macro to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
How about this:
ub IsFilteron() Dim iLastRow As Double Dim numCells As Double iLastRow = Range("B65536").End(xlUp).Row numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count If numCells < iLastRow Then strFltrCrit = AutoFilter_Criteria(Range("B1")) If strFltrCrit = "" Then MsgBox (strFltrCrit) ' This is where your pass macro goes when blank is returned End If 'Call yourmacro End If End Sub -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Almost there Michael........... This one will give a "pass" if ANY column is filtered on, and will return a blank window if the column is not "B". Somehow if we could take that return and allow a "pass" only if the return was NOT blank. Vaya con Dios, Chuck, CABGx3 "Michael" wrote: OK, modify my sub and make sure you also copy the function. Function courtesy of http://www.ozgrid.com/VBA/autofilter-criteria.htm Sub IsFilteron() Dim iLastRow As Double Dim numCells As Double iLastRow = Range("B65536").End(xlUp).Row numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count If numCells < iLastRow Then ' This determines if there is a filter on B strFltrCrit = AutoFilter_Criteria(Range("B1")) 'This returns the criteria used on the filter MsgBox (strFltrCrit) 'Call yourmacro End If End Sub Function AutoFilter_Criteria(Header As Range) As String Dim strCri1 As String, strCri2 As String Application.Volatile With Header.Parent.AutoFilter With .Filters(Header.Column - .Range.Column + 1) If Not .On Then Exit Function strCri1 = .Criteria1 If .Operator = xlAnd Then strCri2 = " AND " & .Criteria2 ElseIf .Operator = xlOr Then strCri2 = " OR " & .Criteria2 End If End With End With AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2 End Function -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Thanks Michael, but I guess I wasn't clear in my post. I only want my macro to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
Thanks Peter, but this one returns "true" no matter what column is filtered
on....or actually if none are and just the Autofilter is turned on. Vaya con Dios Chuck, CABGx3 "Peter T" wrote: Another way (anywhere in col-b) Sub test() MsgBox ColFilterOn(ActiveSheet, "B") MsgBox ColFilterOn(ActiveSheet, 2) End Sub Function ColFilterOn(ws As Worksheet, col) As Boolean Dim af As AutoFilter If VarType(col) = vbString Then col = ws.Range(col & 1).Column End If On Error GoTo errExit Set af = ws.AutoFilter On Error GoTo 0 If Not af Is Nothing Then ColFilterOn = Not Intersect(Columns(col), af.Range) Is Nothing End If errExit: End Function Regards, Peter T "CLR" wrote in message ... Thanks Michael, but I guess I wasn't clear in my post. I only want my macro to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
Maybe I misudnerstood the objective, as written returns true/false if a
filter exists in the column (not necessarily selected). Is this is what you want Sub test() MsgBox ColFilterOn(ActiveSheet, "B") MsgBox ColFilterOn(ActiveSheet, 2) End Sub Function ColFilterOn(ws As Worksheet, col) As Boolean Dim af As AutoFilter, f As Filter If VarType(col) = vbString Then col = ws.Range(col & 1).Column End If On Error GoTo errExit Set af = ws.AutoFilter On Error GoTo 0 If Not af Is Nothing Then If Not Intersect(Columns(col), af.Range) Is Nothing Then For Each f In af.Filters If f.Parent.Range.Column = col Then ColFilterOn = f.On Exit For End If Next End If End If errExit: End Function Regards, Peter T "CLR" wrote in message ... Thanks Peter, but this one returns "true" no matter what column is filtered on....or actually if none are and just the Autofilter is turned on. Vaya con Dios Chuck, CABGx3 "Peter T" wrote: Another way (anywhere in col-b) Sub test() MsgBox ColFilterOn(ActiveSheet, "B") MsgBox ColFilterOn(ActiveSheet, 2) End Sub Function ColFilterOn(ws As Worksheet, col) As Boolean Dim af As AutoFilter If VarType(col) = vbString Then col = ws.Range(col & 1).Column End If On Error GoTo errExit Set af = ws.AutoFilter On Error GoTo 0 If Not af Is Nothing Then ColFilterOn = Not Intersect(Columns(col), af.Range) Is Nothing End If errExit: End Function Regards, Peter T "CLR" wrote in message ... Thanks Michael, but I guess I wasn't clear in my post. I only want my macro to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
Ok Michael........
A tiny bit of tweaking of your last code and this is what I got that does exactly what I want...... Sub IsFilteron() Dim iLastRow As Double Dim numCells As Double iLastRow = Range("B65536").End(xlUp).Row numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count If numCells < iLastRow Then strFltrCrit = AutoFilter_Criteria(Range("B1")) If strFltrCrit = "" Then GoTo 200 Else MsgBox "call your macro" End If Else 200 MsgBox "Do a filter on Column B first" 100 End If End Sub Function AutoFilter_Criteria(Header As Range) As String Dim strCri1 As String, strCri2 As String Application.Volatile With Header.Parent.AutoFilter With .Filters(Header.Column - .Range.Column + 1) If Not .On Then Exit Function strCri1 = .Criteria1 If .Operator = xlAnd Then strCri2 = " AND " & .Criteria2 ElseIf .Operator = xlOr Then strCri2 = " OR " & .Criteria2 End If End With End With AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2 End Function This solves my problem.....your work was brilliant Michael. There is absolutely no way I could have gotten there without your help. I wish I could do more for you than just give you one little checkmark that you were "helpful". Not only do I appreciate your solution, but also for your sticking with me until my problem was solved. You are definately "Grade A" in my book, both as a Programmer, and as a Person. Many thanks again, Vaya con Dios, Chuck, CABGx3 "Michael" wrote: How about this: ub IsFilteron() Dim iLastRow As Double Dim numCells As Double iLastRow = Range("B65536").End(xlUp).Row numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count If numCells < iLastRow Then strFltrCrit = AutoFilter_Criteria(Range("B1")) If strFltrCrit = "" Then MsgBox (strFltrCrit) ' This is where your pass macro goes when blank is returned End If 'Call yourmacro End If End Sub -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Almost there Michael........... This one will give a "pass" if ANY column is filtered on, and will return a blank window if the column is not "B". Somehow if we could take that return and allow a "pass" only if the return was NOT blank. Vaya con Dios, Chuck, CABGx3 "Michael" wrote: OK, modify my sub and make sure you also copy the function. Function courtesy of http://www.ozgrid.com/VBA/autofilter-criteria.htm Sub IsFilteron() Dim iLastRow As Double Dim numCells As Double iLastRow = Range("B65536").End(xlUp).Row numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count If numCells < iLastRow Then ' This determines if there is a filter on B strFltrCrit = AutoFilter_Criteria(Range("B1")) 'This returns the criteria used on the filter MsgBox (strFltrCrit) 'Call yourmacro End If End Sub Function AutoFilter_Criteria(Header As Range) As String Dim strCri1 As String, strCri2 As String Application.Volatile With Header.Parent.AutoFilter With .Filters(Header.Column - .Range.Column + 1) If Not .On Then Exit Function strCri1 = .Criteria1 If .Operator = xlAnd Then strCri2 = " AND " & .Criteria2 ElseIf .Operator = xlOr Then strCri2 = " OR " & .Criteria2 End If End With End With AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2 End Function -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Thanks Michael, but I guess I wasn't clear in my post. I only want my macro to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if Column B is autofiltered
Hi Peter..........
It was just a case of my poor explaining.........if you check my last post to Michael, you will see what I was trying to do. A slight tweak of his final offering solved my problem. Thanks anyway for your interest and submission. Vaya con Dios, Chuck, CABGx3 "Peter T" wrote: Maybe I misudnerstood the objective, as written returns true/false if a filter exists in the column (not necessarily selected). Is this is what you want Sub test() MsgBox ColFilterOn(ActiveSheet, "B") MsgBox ColFilterOn(ActiveSheet, 2) End Sub Function ColFilterOn(ws As Worksheet, col) As Boolean Dim af As AutoFilter, f As Filter If VarType(col) = vbString Then col = ws.Range(col & 1).Column End If On Error GoTo errExit Set af = ws.AutoFilter On Error GoTo 0 If Not af Is Nothing Then If Not Intersect(Columns(col), af.Range) Is Nothing Then For Each f In af.Filters If f.Parent.Range.Column = col Then ColFilterOn = f.On Exit For End If Next End If End If errExit: End Function Regards, Peter T "CLR" wrote in message ... Thanks Peter, but this one returns "true" no matter what column is filtered on....or actually if none are and just the Autofilter is turned on. Vaya con Dios Chuck, CABGx3 "Peter T" wrote: Another way (anywhere in col-b) Sub test() MsgBox ColFilterOn(ActiveSheet, "B") MsgBox ColFilterOn(ActiveSheet, 2) End Sub Function ColFilterOn(ws As Worksheet, col) As Boolean Dim af As AutoFilter If VarType(col) = vbString Then col = ws.Range(col & 1).Column End If On Error GoTo errExit Set af = ws.AutoFilter On Error GoTo 0 If Not af Is Nothing Then ColFilterOn = Not Intersect(Columns(col), af.Range) Is Nothing End If errExit: End Function Regards, Peter T "CLR" wrote in message ... Thanks Michael, but I guess I wasn't clear in my post. I only want my macro to run IF the Autofilter is on for Column B, AND a selection has been made in Column B. Column B is a list of names, and I only want to run the macro against ONE SELECTED name. How can I modify your code to do that? Vaya Con Dios, Chuck, CABGx3 "Michael" wrote: You can modify this instruction to be run on one or multiple columns; as is, it will check in the whole sheet. If Worksheets("Sheet1").AutoFilterMode Then isOn = "On" Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "CLR" wrote: Hi All..... I have a macro that I would like to first check if the sheet1 has been Autofiltered for any selection in Column B before it will continue to run.......the sheet may or may not be also Autofiltered for other columns, but it must also be Autofiltered for a selection in column B to qualify. Any help would be much appreciated. TIA Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete some column headings from an autofiltered range. | Excel Worksheet Functions | |||
test if column isblank | Excel Worksheet Functions | |||
Selecting Column of Visible AutoFiltered Cells. | Excel Programming | |||
Sum one column after capmaring test from teo more columns | Excel Worksheet Functions | |||
test for date in column question | Excel Worksheet Functions |