Sorry in advance. I'm fairly new to VBA but diving in as far as I can. Have a couple of books I'm looking so I can ensure my foundational knowledge will be solid. Someday. I've looked and looked for my answer on my current project but I'm not finding it. So, alas I'm raising my hand for help. This seems like a great forum to do that on.

So I have an excel sheet where I had to reformat and rename information to transfer data to another workbook. While my ultimate plan is to someday have the workbooks talk to each other and we won't be updating it manually at all, I'm taking smaller steps for now. So I thought I'd at least automate what I can so it really is just transferring data. I've gotten all of the other parts of the macro to work from formatting data to the pivot tables to get totals. There is one part I'm stumped on and that is deleting only a partial row and then shifting the data up.

I have information in columns A - F and then again in I - N. I only want the data in cells A - F to move up if the value in the F column matches the word 'production'. I want all columns after F to stay put.

Here is the coding I have so far. But I'm just not knowledgeable enough even through trial and error to fix this.

Dim i As Long
For i = Range("F" & Rows.Count).End(x1Up).Row To 1 Step -1
If Range("F" & i).Value = "production" Then Range("A" & i).Resize(1, 6).delete Shift=x1Up
Next i
End Sub


I'm getting a Run-time error '1004' that says Application-defined or object-defined error. It is showing that it is happening on the For statement.

Any help anyone can give would be so greatly appreciated!!!