Syntax Error

[Damian37]

Hello All,
After creating code to copy my filtered data within my raw data tab, I realized there is a value coming back that I would like to exclude. I'm currently filtering on "High", "Moderate-High". HOwever I'm also retrieving the value "Moderate" alone. I'm trying to exclude it with the following code, but I'm receiving a syntax error. I've tried moving the "Criteria2" portion to different areas, but nothing has worked so far.

Sub FilterMultipleCriteria()
'
 '   On Error Resume Next
    
' Filter by Multiple Criteria

 Application.DisplayAlerts = False
  On Error Resume Next
  Sheets("Sheet1").Delete
  On Error GoTo 0
  Sheets.Add After:=Sheets(Sheets.Count)
  ActiveSheet.Name = "Sheet1"


    Dim ws As Worksheet
    Dim FilterRange As Range
    Dim criteriaArray As Variant
    Dim CopyRange As Range
    Dim DestRange As Range
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Report Data")
    Set FilterRange = ws.Range("E1:EF" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row)
    Set CopyRange = FilterRange.SpecialCells(xlCellTypeVisible)
    Set DestRange = Sheets("Sheet1").Range("A1")
    
        criteriaArray = Array("High", "Moderate-High")
    
    With ws
        .AutoFilterMode = False
        
        FilterRange.AutoFilter Field:=1, Criteria1:=criteriaArray,  _
        Operator:=xlFilterValues,
        Criteria2:="<>*Moderate*"
        
        
        
    End With
    
CopyRange.Copy DestRange

End Sub

Any and all help is greatly appreciated. Thanks!

D

 

[Damian37]

Just saw you updated code. I really need to see your actual data to be able to assist further.

I’ve uploaded a sample doc to Google drive. Here’s the link: All Items- CAPA Tracker Fields Generic shareable - Marcro Unifinished Ver 2.xlsm

 

[kevin9999]

Thank you. So is it column E or column F that you want the filter applied - or both? Also, your data only goes as far as column AO but your code specifies column EF - why is that?

 

[Damian37]

Thank you. So is it column E or column F that you want the filter applied - or both? Also, your data only goes as far as column AO but your code specifies column EF - why is that?

I guess I only added EF, because I'm not filtering any other columns. Could that be part of the reason it's not copying properly? I need the filters applied to both columns E and F. And right now, It's filtering, however, it's including a rating of Moderate, and I only want to bring back values that have ratings of High and Moderate-High. There shouldn't be any instances in which one column would be High and the other column be a value other than Moderate-High. So I just need the filtering to no longer include the rating Moderate.

 

[kevin9999]

OK, we're getting there. Please clarify these 3 points:
1. What filter(s) do you want applied to column E
2. What filter(s) to you want applied to column F
3. What range (which columns) do you want copied into the newly created "Sheet1"

 

[Damian37]

OK, we're getting there. Please clarify these 3 points:
1. What filter(s) do you want applied to column E
2. What filter(s) to you want applied to column F
3. What range (which columns) do you want copied into the newly created "Sheet1"

Okay,
1. Filter rows in column E to only show High and Moderate-High ratings. Exclude Moderate, Low
2. Filter rows in column F to only show High and Moderate-High ratings. Exclude Moderate, Low
3. Copy the newly filtered data and have it saved onto Sheet1.

Bonus: The ability to run macro in Workbook as a template so the raw data within the "Report Data" tab can be pasted over and have the macro run again into an existing sheet that will have calculated columns that I will add after the macro runs successfully so these calculated columns will update within column AP-? every time the macro is run. Basically, not to have the new sheet deleted, so the new columns I create will still be there when I run the macro using new raw data. I hope I explained that well. Let me know if you have any further questions. Thanks!

D.

 

[kevin9999]

1. Filter rows in column E to only show High and Moderate-High ratings. Exclude Moderate, Low
2. Filter rows in column F to only show High and Moderate-High ratings. Exclude Moderate, Low

You do realise that if you exclude Moderate from column E, you could be filtering out Moderate-High lines in column F? Look at row 81 of your sample data & you'll see what I mean.

Apologies about the "Bonus" part, I'm afraid I don't follow it at all. Let's get this first bit right to start with & we'll look at it afterwards.

 

[Damian37]

You do realise that if you exclude Moderate from column E, you could be filtering out Moderate-High lines in column F? Look at row 81 of your sample data & you'll see what I mean.

Apologies about the "Bonus" part, I'm afraid I don't follow it at all. Let's get this first bit right to start with & we'll look at it afterwards.

I see what you mean. Let me reach out to PM to let them know about this. It may be a case of as long as one of the columns has Moderate-High or High.

 

[kevin9999]

The same in reverse, look at row 3. If moderate is excluded from Column F, you lose a Moderate-High in column E.

 

[Damian37]

You do realise that if you exclude Moderate from column E, you could be filtering out Moderate-High lines in column F? Look at row 81 of your sample data & you'll see what I mean.

Apologies about the "Bonus" part, I'm afraid I don't follow it at all. Let's get this first bit right to start with & we'll look at it afterwards.

My PM just let me know that it's fine if one of the columns contains a rating of Moderate as long as the other column has a rating of Moderate-High or High.

 

[kevin9999]

OK, please try the following on a copy of your workbook. Please do not change, add to or delete any part of the code before you try it.

Option Explicit
Sub FilterMultipleCriteria()
    Dim ws As Worksheet, r As Range, criteriaArray

    'Add the new Sheet1
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Sheet1").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Sheet1"
    
    'Set the data range & criteria
    Set ws = Worksheets("Report Data")
    Set r = ws.Range("E1:AO" & ws.Cells(Rows.Count, "E").End(xlUp).Row)
    criteriaArray = Array("High", "Moderate-High")
    
    'Filter & copy
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    With r
        .AutoFilter 1, criteriaArray, 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            .Copy Worksheets("Sheet1").Range("A1")
        End If
    End With
    Worksheets("Sheet1").Columns("A:B").EntireColumn.AutoFit
    ws.AutoFilter.ShowAllData
    
End Sub