Merging VBA for different Ranges.

M

mxalex229

New Member
Joined
Feb 9, 2021
Messages
11
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have two Ranges with the same code. Each works separately but not together. I want the double click event to specify which column to filter in the target column based on the range of the cell that is selected.
Example
If cell is in range C2:AH38 I want it to filter column 1 and 12
If cell is in range C41:AH45 I want it to filter column 11 and 12

The split in the code for the ranges is after case 34

How can I do this?


VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("C41:AH45")) Is Nothing Then Exit Sub
    Select Case Target.Column
    Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
    Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
    Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -13)
                .AutoFilter 12, "Placeholder"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 11, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select


    If Intersect(Target, Range("C2:AH38")) Is Nothing Then Exit Sub
    Select Case Target.Range
        Case Is = 3
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -2)
                .AutoFilter 12, "Insurance Package"
            End With
        Case Is = 4
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -3)
                .AutoFilter 12, "A valid fee for procedure"
            End With
    Case Is = 5
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -4)
                .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
            End With
    Case Is = 6
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -5)
                .AutoFilter 12, "Procedure code is missing"
            End With
    Case Is = 7
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -6)
                .AutoFilter 12, "Provider(Null)"
            End With
    Case Is = 8
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -7)
                .AutoFilter 12, "Provider"
            End With
    Case Is = 9
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -8)
                .AutoFilter 12, "Relationship"
            End With
    Case Is = 10
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -9)
                .AutoFilter 12, "Drug Unit Qualifier"
            End With
        Case Is = 11
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -10)
                .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
            End With
    Case Is = 12
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -11)
                .AutoFilter 12, "ICD-10 Diagnosis Code"
            End With
    Case Is = 13
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -12)
                .AutoFilter 12, "Insurance Package(Direct)"
            End With
    Case Is = 14
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -13)
                .AutoFilter 12, "Placeholder"
            End With
    Case Is = 15
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -14)
                .AutoFilter 12, "Adjustments are only supported for single claims."
            End With
    Case Is = 16
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -15)
                .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
            End With
    Case Is = 17
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -16)
                .AutoFilter 12, "Invalid segment ordering"
            End With
    Case Is = 18
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -17)
                .AutoFilter 12, "Department"
            End With
    Case Is = 19
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -18)
                .AutoFilter 12, "NDC"
            End With
    Case Is = 20
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -19)
                .AutoFilter 12, "All mappings for this message have been completed."
            End With
    Case Is = 21
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -20)
                .AutoFilter 12, "Department(Null)"
            End With
    Case Is = 22
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -21)
                .AutoFilter 12, "COUNTRY 10028   must be mapped."
            End With
    Case Is = 23
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -22)
                .AutoFilter 12, "Procedure code is missing  Provider(Null)"
            End With
    Case Is = 24
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -23)
                .AutoFilter 12, "Unexpectedly large units received"
            End With
    Case Is = 25
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -24)
                .AutoFilter 12, ""
            End With
    Case Is = 26
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -25)
                .AutoFilter 10, "Under 30"
            End With
    Case Is = 27
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -26)
                .AutoFilter 10, "30-59"
            End With
    Case Is = 28
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -27)
                .AutoFilter 10, "60-89"
            End With
    Case Is = 29
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -28)
                .AutoFilter 10, "90-119"
            End With
    Case Is = 30
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -29)
                .AutoFilter 10, "120-149"
            End With
    Case Is = 31
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -30)
                .AutoFilter 10, "150-209"
            End With
    Case Is = 32
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -31)
                .AutoFilter 10, "210-269"
            End With
    Case Is = 33
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -32)
                .AutoFilter 10, "270-364"
            End With
    Case Is = 34
            With Sheets("Table Data").Cells(1, 1).CurrentRegion
                .AutoFilter 1, Target.Offset(, -33)
                .AutoFilter 10, "365"
            End With
    End Select
End Sub
 
Sort by date Sort by votes

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I see what you mean, it maybe because the data on the other sheet is a Query, but the code from post#2 works for both ranges
VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("C41:AH45")) Is Nothing Then
      Select Case Target.Column
      Case Is = 3
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -2)
                  .AutoFilter 12, "Insurance Package"
              End With
      Case Is = 4
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -3)
                  .AutoFilter 12, "A valid fee for procedure"
              End With
      Case Is = 5
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -4)
                  .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
              End With
      Case Is = 6
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -5)
                  .AutoFilter 12, "Procedure code is missing"
              End With
      Case Is = 7
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -6)
                  .AutoFilter 12, "Provider(Null)"
              End With
      Case Is = 8
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -7)
                  .AutoFilter 12, "Provider"
              End With
      Case Is = 9
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -8)
                  .AutoFilter 12, "Relationship"
              End With
      Case Is = 10
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -9)
                  .AutoFilter 12, "Drug Unit Qualifier"
              End With
      Case Is = 11
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -10)
                  .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
              End With
      Case Is = 12
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -11)
                  .AutoFilter 12, "ICD-10 Diagnosis Code"
              End With
      Case Is = 13
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -12)
                  .AutoFilter 12, "Insurance Package(Direct)"
              End With
      Case Is = 14
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -13)
                  .AutoFilter 12, "Placeholder"
              End With
      Case Is = 15
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -14)
                  .AutoFilter 12, "Adjustments are only supported for single claims."
              End With
      Case Is = 16
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -15)
                  .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
              End With
      Case Is = 17
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -16)
                  .AutoFilter 12, "Invalid segment ordering"
              End With
      Case Is = 18
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -17)
                  .AutoFilter 12, "Department"
              End With
      Case Is = 19
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -18)
                  .AutoFilter 12, "NDC"
              End With
      Case Is = 20
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -19)
                  .AutoFilter 12, "All mappings for this message have been completed."
              End With
      Case Is = 21
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -20)
                  .AutoFilter 12, "Department(Null)"
              End With
      Case Is = 22
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -21)
                  .AutoFilter 12, "COUNTRY 10028   must be mapped."
              End With
      Case Is = 23
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -22)
                  .AutoFilter 12, "Procedure code is missing  Provider(Null)"
              End With
      Case Is = 24
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -23)
                  .AutoFilter 12, "Unexpectedly large units received"
              End With
      Case Is = 25
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -24)
                  .AutoFilter 12, ""
              End With
      Case Is = 26
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -25)
                  .AutoFilter 10, "Under 30"
              End With
      Case Is = 27
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -26)
                  .AutoFilter 10, "30-59"
              End With
      Case Is = 28
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -27)
                  .AutoFilter 10, "60-89"
              End With
      Case Is = 29
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -28)
                  .AutoFilter 10, "90-119"
              End With
      Case Is = 30
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -29)
                  .AutoFilter 10, "120-149"
              End With
      Case Is = 31
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -30)
                  .AutoFilter 10, "150-209"
              End With
      Case Is = 32
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -31)
                  .AutoFilter 10, "210-269"
              End With
      Case Is = 33
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -32)
                  .AutoFilter 10, "270-364"
              End With
      Case Is = 34
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 11, Target.Offset(, -33)
                  .AutoFilter 10, "365"
              End With
      End Select
    ElseIf Not Intersect(Target, Range("C2:AH38")) Is Nothing Then
      Select Case Target.Column
          Case Is = 3
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -2)
                  .AutoFilter 12, "Insurance Package"
              End With
          Case Is = 4
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -3)
                  .AutoFilter 12, "A valid fee for procedure"
              End With
      Case Is = 5
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -4)
                  .AutoFilter 12, "Primary ICD-10 diagnosis is missing."
              End With
      Case Is = 6
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -5)
                  .AutoFilter 12, "Procedure code is missing"
              End With
      Case Is = 7
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -6)
                  .AutoFilter 12, "Provider(Null)"
              End With
      Case Is = 8
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -7)
                  .AutoFilter 12, "Provider"
              End With
      Case Is = 9
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -8)
                  .AutoFilter 12, "Relationship"
              End With
      Case Is = 10
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -9)
                  .AutoFilter 12, "Drug Unit Qualifier"
              End With
          Case Is = 11
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -10)
                  .AutoFilter 12, "Sum of payments and adjustments is greater than sum of charges"
              End With
      Case Is = 12
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -11)
                  .AutoFilter 12, "ICD-10 Diagnosis Code"
              End With
      Case Is = 13
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -12)
                  .AutoFilter 12, "Insurance Package(Direct)"
              End With
      Case Is = 14
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -13)
                  .AutoFilter 12, "Placeholder"
              End With
      Case Is = 15
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -14)
                  .AutoFilter 12, "Adjustments are only supported for single claims."
              End With
      Case Is = 16
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -15)
                  .AutoFilter 12, "Units cannot be a zero or a negative number (0.00)"
              End With
      Case Is = 17
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -16)
                  .AutoFilter 12, "Invalid segment ordering"
              End With
      Case Is = 18
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -17)
                  .AutoFilter 12, "Department"
              End With
      Case Is = 19
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -18)
                  .AutoFilter 12, "NDC"
              End With
      Case Is = 20
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -19)
                  .AutoFilter 12, "All mappings for this message have been completed."
              End With
      Case Is = 21
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -20)
                  .AutoFilter 12, "Department(Null)"
              End With
      Case Is = 22
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -21)
                  .AutoFilter 12, "COUNTRY 10028   must be mapped."
              End With
      Case Is = 23
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -22)
                  .AutoFilter 12, "Procedure code is missing  Provider(Null)"
              End With
      Case Is = 24
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -23)
                  .AutoFilter 12, "Unexpectedly large units received"
              End With
      Case Is = 25
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -24)
                  .AutoFilter 12, ""
              End With
      Case Is = 26
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -25)
                  .AutoFilter 10, "Under 30"
              End With
      Case Is = 27
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -26)
                  .AutoFilter 10, "30-59"
              End With
      Case Is = 28
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -27)
                  .AutoFilter 10, "60-89"
              End With
      Case Is = 29
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -28)
                  .AutoFilter 10, "90-119"
              End With
      Case Is = 30
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -29)
                  .AutoFilter 10, "120-149"
              End With
      Case Is = 31
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -30)
                  .AutoFilter 10, "150-209"
              End With
      Case Is = 32
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -31)
                  .AutoFilter 10, "210-269"
              End With
      Case Is = 33
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -32)
                  .AutoFilter 10, "270-364"
              End With
      Case Is = 34
              With Sheets("Table Data").Cells(1, 1).CurrentRegion
                  .AutoFilter 1, Target.Offset(, -33)
                  .AutoFilter 10, "365"
              End With
      End Select
   End If
End Sub
 
Upvote 0
The data is from a Query. My apologies for not notating as much sooner. Is that the root of the issue with the VBA?
 
Upvote 0
The code I posted works for me in your workbook, are you saying it doesn't work for you?
 
Upvote 0
After re-checking it I now see that the codes is working correctly. It does not appear as such because its not clearing previously selected filtered content, so when you switch from one range to another its not clearing the different column from the other selected range.

Solution was to add " If Sheet1.AutoFilterMode Then Sheet1.AutoFilterMode = False "

Thank you so much for the solution!
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
493
Alex Blakenburg
A
J
  • Question
Merge 2 VBA code help
Replies
1
Views
465
Joe4
Joe4
M
  • Solved
Replacing delete with a value
Replies
3
Views
158
Alex Blakenburg
A
J
  • Question
Marco to add tabs to WKS with values from column A
Replies
4
Views
318
Skyybot
Skyybot
A
  • Question
VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section
Replies
1
Views
185
John_w
J

Forum statistics

Threads
1,214,946
Messages
6,122,401
Members
449,081
Latest member
JAMES KECULAH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top