Results 1 to 2 of 2

Thread: How to use Macros to Filter less than or equal to Cell Values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to use Macros to Filter less than or equal to Cell Values

    Hello,

    I want this Macro to filter off less than or equal to, not just equal to. Also, if one of the cells is blank (B2 or C2) I would like it to show 'all' for that selection, rather than 'none.'

    Can anybody help me?

    Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Target.Cells(1, 1)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
    Range("A4:J500").CurrentRegion.AutoFilter field:=9, Criteria1:="<=Target.Value"
    End If
    If Not Intersect(Target, Range("C2")) Is Nothing Then
    Range("A4:J500").CurrentRegion.AutoFilter field:=10, Criteria1:="<=Target.Value"
    End If
    End Sub

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,945
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to use Macros to Filter less than or equal to Cell Values

    Try this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Count > 1 Then Exit Sub
      If Not Intersect(Target, Range("B2, C2")) Is Nothing Then
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
        If Target = "" Then Exit Sub
        If Target.Address(0, 0) = "B2" Then
          Range("A4:J500").CurrentRegion.AutoFilter field:=9, Criteria1:="<=" & Target.Value
        Else
          Range("A4:J500").CurrentRegion.AutoFilter field:=10, Criteria1:="<=" & Target.Value
        End If
      End If
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •