Syntax:

     

     

     

     

     

    Range:

     

  • Denotes about the range of table\Data

    •  

    Field:

     

  • Column Number within the table defined

    •  

    Criteria:

     

  • It is nothting but condition\depends on our requirement

    •  

    Operator:

     

  • All these are predefined

    • Code:

    Private Sub CommandButton1_Click()
    ActiveSheet.AutoFilterMode = flase
    Range("A1:A25").AutoFilter
    Range("A1:A25").AutoFilter Field:=1, Criteria1:="Apple"
    End Sub

     

     

    Autofilter Add or Remove

     

    Code:

    Private Sub CommandButton1_Click()
    Add Autofilter
    Range("A1").AutoFilter
    Remove Autofilter
    ActiveSheet.AutoFilterMode = False
    Add Autofilter
    Range("A1").AutoFilter
    Second time it removes: Remove Autofilter
    Range("A1").AutoFilter
    End Sub

     

     

    Set Autofilter to a Range

     

    Code:

    Private Sub CommandButton1_Click()
    'Remove filter if exists
    ActiveSheet.AutoFilterMode = False
    'define object variable for filterrange
    Dim filterrange As Range
    'Assing value to filterrange variable
    Set filterrange = Range(("A1"), Cells(Rows.Count, 1).End(xlUp))
    'Auto filter condition
    filterrange.AutoFilter field:=1, Criteria1:="Apple"
    End Sub

     

    Add or Remove Autofilter

     

    Private Sub CommandButton1_Click()
    'add autofilter
    Range("A1").AutoFilter
    End Sub

    Private Sub CommandButton2_Click()
    'remove autofilter
    'Range("A1").AutoFilter
     ActiveSheet.AutoFilterMode = False
    End Sub

     

     

    AutoFilter - Top 10

     

    Private Sub CommandButton1_Click()
    'To enable Autofilter
     Range("A1").AutoFilter Range("A1").AutoFilter Field:=4, _
    Criteria1:="96", _
    Operator:=xlTop10Items
    ' To remove Autofilter
     Range("A1").AutoFilter
    End Sub

     

     

     

    AutoFilter - Select Multiple Values

     

     

  • XLFilterValues:Denotes about Values retrieved through Autofilter

    •  

    Private Sub CommandButton1_Click()
    Range("A1").AutoFilter
    Conditions mentioned in Array
    Range("A1").AutoFilter field:=2, _
    Criteria1:=Array("Apple", "Banana"), _
    Operator:=xlFilterValues
    End Sub

     

     

     

    AutoFilter - OR Operator

     

    Private Sub CommandButton1_Click()
    Range("A1").AutoFilter
    'Use Or Operator
     Range("A1").AutoFilter field:=2, _
    Criteria1:="Apple", _
    Operator:=xlOr, _
    Criteria2:="Banana"
    End Sub

     

     

     

    AutoFilter - AND Operator _ Alpha

     

    Private Sub CommandButton1_Click()
    Range("A1").AutoFilter
    'Use AND Operator
     Range("A1").AutoFilter field:=2, Criteria1:=">=A", _
    Operator:=xlAnd, Criteria2:="<=C"
    End Sub

     

     

     

    AutoFilter - AND Operator - Numbers

     

    Private Sub CommandButton1_Click()
    Range("A1").AutoFilter
    'Use AND Operator
     Range("A1").AutoFilter field:=4, Criteria1:=">=85", _
    Operator:=xlAnd, Criteria2:="<=100"
    End Sub

     

    Autofilter Vs Sort

     

     

     

     

     

    Retrieve Last Month values

     

    Criteria for Values:

  • XlFilterAboveAverage
  • XlFilterBelowAverage

    •  

    Criteria for Past:

  • XlFilterYesterday
  • XlFilterLastweek
  • XlFilterLastMonth
  • XlFilterLastQuarter
  • XlFilterLastYear

    •  

    Criteria for Current Period:

  • XlFilterToday
  • XlFilterThisweek
  • XlFilterThisMonth
  • XlFilterThisQuarter
  • XlFilterThisYear

    •  

    Criteria for Future:

  • XlFilterTomorrow
  • XlFilterNextWeek
  • XlFilterNextMonth
  • XlFilterNextYear

    • Private Sub CommandButton1_Click()
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=7, _
    Criteria1:=xlFilterLastMonth, _
    Operator:=xlFilterDynamic
    End Sub

     

     

     

    Autofilter - Defining Particular Rage

     

    Private Sub CommandButton1_Click()
    ActiveSheet.Range("$A$1:$F$100").AutoFilter Field:=2, Criteria1:="Banana"
    End Sub

     

     

     

    Autofilter - Sort Syntax

     

     

  • We need to use ADD method to apply SORT
  • ADD method having 5 parameters

    •  

  • Key: Denotes about Sort Field.i.e Range\Range Object

    •  

  • Sorton: Denotes about Criteria, i.e Values,Icon,Color etc

    •  

  • Order: Denotes about Sort Order. i.e XlAscending, XlDescending

    •  

  • CustomOrder: Enables the user to sort by days\Weeks\Months

    •  

    PROPERTIES AND METHODS OF SORT

     

  • Method: Sort.Set: to define range

    •  

  • Property: Sort.Header: denotes whether Header exists or not

    • XLNo(default)

    XLYes

     

  • Property: Sort.Matchcase: denotes whether sort having case sensitive

    • True = For case sensitive

    False: No case sensititve

     

  • Property: Sort.Orientation:

    • XLSortColumns: Sort by columns

    XLSortRows: Sort by Rows

     

    Use WITH keyword to add Properties and Methods

     

    Private Sub CommandButton1_Click()
    'To remove the existing sorting
    Sheets("Sheet2").Sort.SortFields.Clear
    Sort.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, _
    Order:=xlAscending
    With ThisWorkbook.Sheets("Sheet2")
    Sort.SetRange Range("A1").CurrentRegion
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlSortColumns
    .Sort.Apply
    End With
    End Sub
    Private Sub CommandButton2_Click()
    'To remove the existing sorting
    Sheets("Sheet2").Sort.SortFields.Clear
    Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending
    With ThisWorkbook.Sheets("Sheet2")
    Sort.SetRange Range("A1").CurrentRegion
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlSortColumns
    .Sort.Apply
    End With
    End Sub

     

     

    Autofilter - Sort Ascending Order - If Condition

     

    Private Sub CommandButton1_Click()
    Dim j As Boolean
    Q = 2
    Do
    j = False
    For i = 2 To 20
    If Cells(i, 5).Value > Cells(i + 1, 5).Value Then
    Cells(i, 6).Value = Cells(i, 5).Value
    Cells(i, 5).Value = Cells(i + 1, 5).Value
    Cells(i + 1, 5).Value = Cells(i, 6).Value
    End If
    j = True
    Next
    Q = Q + 1
    Loop While j
    End Sub

     

     

     

    Sorting values for Specified Range

     

    Private Sub CommandButton1_Click()
    'To remove the existing sorting
    Sheets("Sheet2").Sort.SortFields.Clear
    'Sheets("Sheet2").Sort.SortFields.Clear
    Sort.SortFields.Add Key:=Range("B25"), SortOn:=xlSortOnValues, _
    Order:=xlAscending
    With ThisWorkbook.Sheets("Sheet2")
    Sort.SetRange Range("A25:F85").Rows
    .Sort.Header = xlYes
    .Sort.MatchCase = False
    .Sort.Orientation = xlSortColumns
    .Sort.Apply
    End With
    End Sub

     

     

     

    Display Sort Dialogue Box

     

    Private Sub CommandButton1_Click()
    Application.Dialogs(xlDialogSort).Show
    End Sub

     

     

    Sort based on custom list

     

    Private Sub CommandButton1_Click()
    Application.AddCustomList listarray:=(Range("I2:I4"))
    Range("A5:G20").Sort key1:=Range("B5"), Order1:=xlDescending, Header:=xlYes
    Application.DeleteCustomList Application.CustomListCount
    End Sub

     

     

    Sort Every 5 Rows

     

    Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 2 To 26 Step 5
    Range(Cells(i, 1), Cells(i + 4, 7)).Sort _
    key1:=Cells(i + 4, 3), _
    order1:=xlAscending, _
    Header:=xlNo
    Range(Cells(i, 1), Cells(i + 4, 7)).Select
    Next
    End Sub

     

     

    Advance Filter - Multiple Conditions

  • It enables the user to execute to multiple filters

    •  

     

    Action: Place where result set should place

     

    XlFilterInPlace: to place in same location

    XlFilterCopy: to place in some other location

     

    Unique: True: to place Unique records, default is false

     

    Advance Filter - Example

     

  • Result displays in sheet3
  • Criteria mentioned in I1:J3

    •  

    Private Sub CommandButton1_Click()
    Range("A1").CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    criteriarange:=Range("I1:J3"), _
    copytorange:=Sheets("Sheet3").Range("A1"), _
    Unique:=False
    End Sub

     

     

     

    Advance Filter - Remove Duplicates

     

    Private Sub CommandButton1_Click()
    Dim r As Range
    Set r = Range("A1:A150")
    r.AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True
    End Sub

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli