Help on Error 1004 Delete method of range class failed

[kit99]

Got two sheets holding data, formatted as table. This format is necessary as I'm using this data in pivot tables and graphs, and number of rows in use may vary from report to report.
When I try to update data in sheets FU and LU, I'm getting an error trying to delete old data. Error only appear when trying to update sheet LU...
Sheets FU and LU holds data in row 1 to 3 that I want to keep, so I'm trying to delete from row 4 and down (A1 to A3 holds data in both tables, and so does A4 and down).
Here's my code:

    'New data sheet FU
        Sheets("FU").Select
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Range("A4:L" & lr).EntireRow.Delete
        
            Windows("ReportLU.xlsx").Activate
            Sheets(1).Select
            lr = Range("A" & Rows.Count).End(xlUp).Row
            Range("A2:L" & lr).Copy
            
        Windows("Dash.xlsm").Activate      
        Sheets("FU").Select
        Range("A4").PasteSpecial

    'New data sheet LU
        Sheets("LU").Select
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Range("A4:L" & lr).EntireRow.Delete
        
            Windows("ReportLU.xlsx").Activate
            Sheets(1).Select
            lr = Range("A" & Rows.Count).End(xlUp).Row
            Range("A2:L" & lr).Copy
            
        Windows("Dash.xlsm").Activate      
        Sheets("LU").Select
        Range("A4").PasteSpecial

Error appear in this line:
Range("A4:L" & lr).EntireRow.Delete
But only when trying to delete from sheet LU...
This is a mystery to me, as both tables in sheet FU and LU and code in use is exactly the same.

Anyone that knows what's going on here? And how to fix it...

 

[Sinon]

Not in any way an expert in VBA but a lot of my issues have been because I failed to indicate in the code which workbook/sheet I want to use when I am switching between workbooks/sheets. Maybe you need to make sure the activated workbook before proceeding to the LU sheet is the one where you have your LU sheet where you want to delete rows?

I tried your code but skipped on the copy and paste bits and it works fine for me. Of course, i am not switching between workbooks so I thought that might be the issue.

 

[kit99]

Not in any way an expert in VBA but a lot of my issues have been because I failed to indicate in the code which workbook/sheet I want to use when I am switching between workbooks/sheets. Maybe you need to make sure the activated workbook before proceeding to the LU sheet is the one where you have your LU sheet where you want to delete rows?

I tried your code but skipped on the copy and paste bits and it works fine for me. Of course, i am not switching between workbooks so I thought that might be the issue.

Just tried that, and it didn't help.
And I know I'm in the correct sheet, cos I've tried this code as well:

        Range("N4:N1000").Select
        Selection.EntireRow.Delete

This marks out a range outside my formatted table, so I can see that my range is OK.
And then the vba stops in the second line with the same error... I'm getting a headache here...

 

[BarryL]

whats your value of "lr" then? sounds like it might be less than 4

 

[kit99]

whats your value of "lr" then? sounds like it might be less than 4

Not sure how to find that value...
But I just tried to do this delete-process via the vba recording (delete N4:N1000), and it comes up with a warning-message:

This operation is now allowed. The operation is trying to move cells in a table in the workbook.

What's this? It should be quite easy to delete rows in a formatted table...?

 

[yky]

Not sure how to find that value...

Put a break on the line, run the program, when it stops at the line, enter "lr" in the watch window (Debug/Add Watch...).

 

[Sinon]

I would suggest that you step through the code with F8 and see exactly what is happening in the sheet as the code is running. Use the Locals window to see exactly what value LR takes (you can get it up if you go the VBA editor ->View->Locals Window)

 

[kit99]

Put a break on the line, run the program, when it stops at the line, enter "lr" in the watch window (Debug/Add Watch...).

I've been fiddeling with my table for a while, and suddenly I could delete row manually without any errors.
So I stated up med VBA again, and now it ran right through this sheet as it should.
In fact, this is a quite long code that runs through 7 workbooks with similar sheets and tables in each.
And it all went just fine...

I don't know what I changed, and I still don't understand this.
But as long as it works I'm leaving it alone...
Thanks a lot for suggestions and help!