AutoFilter method of range class failed

[Akbarov]

Hello, I am trying to delete whole row.
If any cell in column A on sheet1 contains a value from sheet2 column P
But for some reason I get error 1004

Can anyone help me please? Where I did a mistake?

Error line

.AutoFilter 1, Array(Arr), 7

    Dim wb As Workbook
    Dim ws As Worksheet, Arr, i As Long
    Dim LR As Long
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    Set sh = wb.Sheets("Sheet2")
    Application.ScreenUpdating = False
   
    ws.Activate
    LR = Cells(Rows.Count, 16).End(xlUp).Row
   
    Arr = ws.Range("P2:P" & LR).Value
    Arr = Application.Transpose(Application.Index(Arr, 0, 1))
   
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = CStr(Arr(i))
    Next i
   
    sh.Activate
    With sh.Range("A3:A2000")
        .AutoFilter 1, Array(Arr), 7
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        .AutoFilter
    End With

And just debugged:
Arr(i) = <Subscript out of range>

 

[Alex Blakenburg]

Can you show me your Sheet1, Column P, make sure you show both Row and Column references.

 

[Alex Blakenburg]

Actually based on your description you have your sheet references back to front.

I am trying to delete whole row.
If any cell in column A on sheet1 contains a value from sheet2 column P

If the above means you are trying to delete rows from Sheet1 just reverse your sheet references to the below.

    Set ws = wb.Sheets("Sheet1")
    Set sh = wb.Sheets("Sheet2")
    Set ws = wb.Sheets("Sheet2")
    Set sh = wb.Sheets("Sheet1")

 

[Akbarov]

Thank you for reply.
I replaced sheet names may be this way will be better.

    Dim wb As Workbook
    Dim ws As Worksheet, Arr, i As Long
    Dim LR As Long
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Backend")
    Set sh = wb.Sheets("MAIN")
    Application.ScreenUpdating = False
    
    ws.Activate
    LR = Cells(Rows.Count, 16).End(xlUp).Row
    
    Arr = ws.Range("P2:P" & LR).Value
    Arr = Application.Transpose(Application.Index(Arr, 0, 1))
    
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = CStr(Arr(i))
    Next i
    
    sh.Activate
    With sh.Range("A3:A2000")
        .AutoFilter 1, Array(Arr), 7
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        .AutoFilter
    End With

 

[Alex Blakenburg]

Based on those sheet names and those Set commands, the code will look in Backend at Column P, get the 2 values that are there being 2017 and 2018.
Filter the sheet main applying those values to filter column A of the Sheet Main, and delete those rows.

What happens when you run it ?
Does it still give you an error message ? If so what is the message and which line in the code is hightlighted ?
If there is no error does it delete what you are expecting ?

 

[Akbarov]

I get error in following line.

 

[Alex Blakenburg]

Do you know how to use the Watch window ? If so put Arr in the watch window and click on the plus sign to see what is in Arr.
If not, then when it errors out, do the following:
• Double click on the word Arr to select it (doesn't matter which one)
• right click and Add Watch
• Hit ok

Send me a picture of the Watch window if there is a plus sign click on it first to expand it like the below image.
I am login off but can have a look tomorrow.
Share a sample of your work book via dropbox, google drive etc with your code and sample data if you can.

 

[Akbarov]

Here it is. Everything looks fine.
If I can't solve it today, I will share sample file.
Thanks for your time.

 

[Alex Blakenburg]

I get error in following line.

So you are getting an error on the line Arr(i) = CStr(Arr(i)) but when it errors out the watch window is showing what you have in Post #8, that doesn't seem to make sense.
Are you going to be able to share a sample file ?

 

[Akbarov]

Hi Alex, the data in Main was imported from different sheet. I was using xlPasteAllUsingSourceTheme then I replaced it with xlPasteValues it works fine now. Thanks for your time.