none
How to tune auto filter option for our need??? RRS feed

  • Question

  • Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7, Criteria1:="=ECE"       
    End Sub
    Sub Macro2()
    '
    ' Macro2 Macro
    '

    '
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7, Criteria1:="=CSE", _
            Operator:=xlOr, Criteria2:="=ECE"
    End Sub
    Sub Macro3()
    '
    ' Macro3 Macro
    '

    '
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7  
    End Sub

    Sub Macro4()
    '
    ' Macro4 Macro
    '

    '
        Range("A1:I7").Select
        Range("I7").Activate
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7, Criteria1:="=NFO*", _
            Operator:=xlAnd
    End Sub
    Sub Macro5()
    '
    ' Macro5 Macro
    '

    '
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7, Criteria1:="=NFO*", _
            Operator:=xlOr
    End Sub
    Sub Macro6()
    '
    ' Macro6 Macro
    '

    '
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7
    End Sub
    Sub Macro7()
    '
    ' Macro7 Macro
    '

    '
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7, Criteria1:="=*ECE*", _
            Operator:=xlAnd, Criteria2:="=*CSE*"
    End Sub
    Sub Macro8()
    '
    ' Macro8 Macro
    '

    '
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7, Criteria1:= _
            "=*CSE ,ECE*", Operator:=xlAnd
    End Sub
    Sub Macro9()
    '
    ' Macro9 Macro
    '

    '
        ActiveSheet.Range("$A$1:$I$7").AutoFilter Field:=7, Criteria1:="=*ECE*", _
            Operator:=xlAnd
    End Sub

    The above mentioned code is recorded macro, i have recorded macro for different combinations , but what I need is I want to tune the auto filter fields as per the combinations , according to that the filter should filter the data's

    these are the two fields AutoFilter and criteria, kindly do the needful and thanks in advance!!!


    Wednesday, July 27, 2016 11:58 AM

All replies

  • Cannot understand the question or what you want to do. Need better explanation.

    Regards, OssieMac

    Thursday, July 28, 2016 5:47 AM

  • Friday, August 5, 2016 4:45 AM
  • Do you want the code to loop through all of the department names and write data for each department to separate text files or do you want to just select an individual department name and just write the data for the selected department to a text file?

    Regards, OssieMac

    Friday, August 5, 2016 7:24 AM
  • I need the selected(filtered_data means combination of departments like and , or logic ) department name and write to as a text file... 

    suppose if I filter the ECE and MECH department name means only those data's have to store in a text file in csv format not all the departments and tanQ for your timely help!!!

    with regards,

    Parthiban

    Friday, August 5, 2016 8:03 AM
  • for e.g. If I set the filter option to IT means I need the ouptut like this....

    SUJITHA,415187,89,IT,HARISH

    ANITHA,445143,95,IT,SELVAM

    and these details write to a text file in csv format....

    with regards,

    Parthiban 





    Friday, August 5, 2016 8:21 AM
  • Thank you for the info. I am sure I understand now I can help but it is getting late in the day in my part of the world so might be tomorrow before I get it done.

    Regards, OssieMac

    Friday, August 5, 2016 8:46 AM
  • have a gorgeous night!!!
    Friday, August 5, 2016 8:50 AM
  • Try the code below. See the comments where you might need to edit the code and also where I have provided optional code. The code between the hash (#) lines copies data only with no column headers and the optional code between the asterisk (*) lines copies the column headers and the data. If you want to change then only use one of the options and uncomment and comment out the other option.

    Sub FiltDataToTextFile()
        Dim ws As Worksheet
        Dim wbNew As Workbook
        Dim lngUserShts As Long
        Dim rngVisible As Range
        Dim strPath As String
        Dim varCsvFile As Variant   'Use variant to accomodate Cancel on InputBox
        Dim strCsvPathFile As String
        
        Set ws = Worksheets("Sheet1")       'Edit "Sheet1" to your worksheet name
        
        On Error Resume Next    'Suppress error if filters not set
        'Ensure filter has been set
        If Not ws.FilterMode Then
            MsgBox "No filters set. Processing terminated."
            Exit Sub
        End If
        On Error GoTo 0
        
        strPath = ThisWorkbook.Path & "\"   'Set path to same as this workbook
        
        'strPath = "C:\Users\User\Documents\CSV Files\"  'Alternative example of setting path
        
        varCsvFile = Application.InputBox(Prompt:="Enter file name for save.", Type:=2)
        
        If varCsvFile = False Then  'Returns False if user clicks Cancel
            MsgBox "User cancelled. Processing terminated."
            Exit Sub
        End If
        
        strCsvPathFile = strPath & varCsvFile
        
        
        '#################################################################
        'Code between # lines copies data only WITHOUT column headers
        With ws.AutoFilter.Range
            Set rngVisible = .Offset(1, 0) _
                            .Resize(.Rows.Count - 1, .Columns.Count) _
                            .SpecialCells(xlCellTypeVisible)
        End With
        '#################################################################
        
        '*****************************************************************
        'Code between asterisk lines copies data PLUS column headers
        'AutoFilter.Range is dynamic range for AutoFilter irrespective of number of rows
        'With ws.AutoFilter.Range
        '    Set rngVisible = .SpecialCells(xlCellTypeVisible)
        'End With
        '*****************************************************************
        
        'Save the users option setting for number of workbook sheets in new workbook
        lngUserShts = Application.SheetsInNewWorkbook
        
        'Change number of worksheets in new workbook to One (CSV can only save one sheet)
        Application.SheetsInNewWorkbook = 1
        
        Set wbNew = Workbooks.Add
        
        'Reapply the users number of sheets option
        Application.SheetsInNewWorkbook = lngUserShts
        
        rngVisible.Copy Destination:=wbNew.Worksheets(1).Cells(1, 1)
        
        Application.DisplayAlerts = False   'Suppress alert messages
        wbNew.SaveAs Filename:=varCsvFile, _
                                FileFormat:=xlCSVMSDOS, _
                                CreateBackup:=False
        wbNew.Close
        Application.DisplayAlerts = True    'Turn alerts on again
        
        'Following code optional to reset filters to ShowAllData
        'Uncomment if required
        'With ws
        '    If .AutoFilterMode Then
        '        If .FilterMode Then
        '            .ShowAllData
        '        End If
        '    End If
        'End With
        
        MsgBox "New csv file " & strCsvPathFile & " saved."
    
    End Sub
    


    Regards, OssieMac

    Friday, August 5, 2016 9:44 PM