Macro modification

[Jeeremy7]

Hello I have this macro

Sub Delete_Duplicate_Codes_AURA_Data()

   'Delete amount already invoiced last month
    Const F = "F7:F#&""¤""&G7:G#"
    Dim V, R&
    With Sheet10
        V = Filter(.Evaluate(Replace("TRANSPOSE(IF(F7:F#>""""," & F & "))", "#", .Cells(.Rows.Count, 6).End(xlUp).Row)), False, False)
    End With
    With Sheet7
        V = Application.Match(.Evaluate(Replace(F, "#", .Cells(.Rows.Count, 6).End(xlUp).Row)), V, 0)
        Application.ScreenUpdating = False
    For R = UBound(V) To 1 Step -1
        If IsNumeric(V(R, 1)) Then .Rows(R + 6).Delete
    Next
        Application.ScreenUpdating = True
    End With
    
    Range("A1").Select
    
End Sub

I would like the line not be deleted if the data in column 6 is ''XXX''' I think the problem is to add a exception at If IsNumeric(V(R, 1)) Then .Rows(R + 6).Delete but I'm not sure how to ?

Thanks !

 

[iggydarsa]

For R = UBound(V) To 1 Step -1
       If V(R, 6) <>"XXX" Then
             If IsNumeric(V(R, 1)) Then .Rows(R + 6).Delete
       End If
Next

Not sure if it will solve your issue but hoping at least the concept might give you an idea.

 

[Jeeremy7]

I get a compile error Next without for

Sub Delete_Duplicate_Codes_AURA_Data()

   'Delete amount already invoiced last month
    Const F = "F7:F#&""¤""&G7:G#"
    Dim V, R&
    With Sheet10
        V = Filter(.Evaluate(Replace("TRANSPOSE(IF(F7:F#>""""," & F & "))", "#", .Cells(.Rows.Count, 6).End(xlUp).Row)), False, False)
    End With
    With Sheet7
        V = Application.Match(.Evaluate(Replace(F, "#", .Cells(.Rows.Count, 6).End(xlUp).Row)), V, 0)
        Application.ScreenUpdating = False
    For R = UBound(V) To 1 Step -1
        If V(R, 6) <> "XXX" Then
        If IsNumeric(V(R, 1)) Then .Rows(R + 6).Delete
    Next
        Application.ScreenUpdating = True
    End With
    
    Range("A1").Select
    
End Sub

 

[iggydarsa]

you are missing "End If"

If V(R, 6) <> "XXX" Then
        If IsNumeric(V(R, 1)) Then .Rows(R + 6).Delete
End If

 

[Jeeremy7]

oups....
Now it tells me
Run-time error 9
Subscript out of range ?

    Const F = "F7:F#&""¤""&G7:G#"
    Dim V, R&
    With Sheet10
        V = Filter(.Evaluate(Replace("TRANSPOSE(IF(F7:F#>""""," & F & "))", "#", .Cells(.Rows.Count, 6).End(xlUp).Row)), False, False)
    End With
    With Sheet7
        V = Application.Match(.Evaluate(Replace(F, "#", .Cells(.Rows.Count, 6).End(xlUp).Row)), V, 0)
        Application.ScreenUpdating = False
    For R = UBound(V) To 1 Step -1
        If V(R, 6) <> "Josh Visa RBC" Then
        If IsNumeric(V(R, 1)) Then .Rows(R + 6).Delete
        End If
    Next
        Application.ScreenUpdating = True
    End With
    
    Range("A1").Select
    
End Sub