programmatic advanced filter
 

hi all,

im a lil new to excel automation so forgive me if this is a little easy
for you all.

i have been using an advanced filter which requires the following

input range
input criteria
output range <or location

i need to automate this as I am finding that many people just cant
grasp the advanced filter for reasons which elude me.

anyway Im very familiar with vbs and vb just not at all familiar with
vba.

any thoughts on where to start looking.


--
jmcfadyen
------------------------------------------------------------------------
jmcfadyen's Profile: http://www.excelforum.com/member.php...o&userid=34733
View this thread: http://www.excelforum.com/showthread...hreadid=544956

programmatic advanced filter
 

Hi J,

Try turning on the macro recorder while you perform the requisite steps
manually.
This will provide you with base code which can be rdited to enable more
general application.

If you experiene problems with such editing, post back with rhe problematic
code.


---
Regards,
Norman


"jmcfadyen" wrote
in message ...

hi all,

im a lil new to excel automation so forgive me if this is a little easy
for you all.

i have been using an advanced filter which requires the following

input range
input criteria
output range <or location

i need to automate this as I am finding that many people just cant
grasp the advanced filter for reasons which elude me.

anyway Im very familiar with vbs and vb just not at all familiar with
vba.

any thoughts on where to start looking.


--
jmcfadyen
------------------------------------------------------------------------
jmcfadyen's Profile:
http://www.excelforum.com/member.php...o&userid=34733
View this thread: http://www.excelforum.com/showthread...hreadid=544956

programmatic advanced filter
 

Hi
I use this to do a filter when I have three or more items to filter on
in one column. you may be able to adapt it for your filter.
I'm assuming the user is selecting multiple items in a listbox called
Grouplistdata. The count of the selected items is Groupcount.
I've added a sub at the end which I use to add sheets.

CODE SNIPPET:
'Remove any filter present on the sheet
With Worksheets("Test")
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
End With
Select Case GroupCount 'a listbox selection count
'Check the user hasn't clicked OK on the userform without selecting
anything
Case 0
Exit Sub 'nothing happens and form is still visible
'Use AutoFilter if GroupCount is 1 or 2
Case 1
GroupRange.AutoFilter Field:=1,
Criteria1:=CStr(Grouplistdata(1))
Case 2
GroupRange.AutoFilter Field:=1,
Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr, _
Criteria2:=CStr(Grouplistdata(2))
Case Is 2
'Start by inserting a worksheet and creating a Criteria Range to put
into AdvancedFilter
'addSheet first removes the sheet, so it is always a fresh sheet
AddSheet "WorkSpaceSheet" 'now the active sheet
With ActiveWorkbook.Worksheets("WorkSpaceSheet")
.Visible = False
.Cells(1, 1).Value = "Group" 'same as name on worksheet
"Test"
i = 1 'set a counter and put group items below "Group"
For Each Item In Grouplistdata
i = i + 1
.Cells(i, 1).Value = Item
Next Item

Set GroupCriteria = .Cells(1, 1).CurrentRegion
End With 'worksheet
'Filter the list
Worksheets("Test").Activate
GroupRange.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=GroupCriteria
End Select

END CODE SNIPPET

Public Sub AddSheet(TheSheetName As String)
Dim wsNew As Worksheet 'we will insert a fresh worksheet
With ActiveWorkbook
On Error Resume Next
Application.DisplayAlerts = False
.Worksheets(TheSheetName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
'If worksheet is not there the error in deletion is ignored
'Add a new sheet
Set wsNew = .Worksheets.Add 'Makes wsNew the active sheet
wsNew.Name = TheSheetName
End With
Set wsNew = Nothing
End Sub

regards
Paul