I have a very simple vba macro, but I would delete row after I cut row to another sheet

[Deductedsolid]

I am new to vba macros and need help. I have a very simple vba macro, but I would like delete row after I cut row to another sheet and then shift remaining cells up

a = Worksheets("Work Order Tracking Form").Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To a

    If Worksheets("Work Order Tracking Form").Cells(i, 8).Value = "Complete" Then

        Worksheets("Work Order Tracking Form").Rows(i).EntireRow.Cut 
        Worksheets("Completed 2020").Activate
        b = Worksheets("Completed 2020").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Completed 2020").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("Work Order Tracking Form").Activate

    End If

Next
Application.CutCopyMode = False

ThisWorkbook.Worksheets("Work Order Tracking Form").Cells(1, 1).Select

[alansidman]

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[Deductedsolid]

Ok, thank you.

[jindon]

try something like this

dim x as range
a = Worksheets("Work Order Tracking Form").Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To a

If Worksheets("Work Order Tracking Form").Cells(i, 8).Value = "Complete" Then
    if x is nothing then
        set x=Worksheets("Work Order Tracking Form").Cells(i, 8)
    else
        set x = union(x,Worksheets("Work Order Tracking Form").Cells(i, 8))
    end if
end if
next
if not x is nothing then
    x.entirerow.copy Worksheets("Completed 2020").Cells(Rows.Count, 1).End(xlUp)(2)
    x.entirerow.delete
end if
Application.CutCopyMode = False

ThisWorkbook.Worksheets("Work Order Tracking Form").Cells(1, 1).Select

[Deductedsolid]

I'm getting an error on the last End If "end if without block if"

[jindon]

OOps, the code has been edited.

You could also do it with AutoFilter, if you have header.

[Deductedsolid]

Now I'm getting an error

x.entirerow.delete

line. Sorry I'm new to vba and struggling

[jindon]

Can you show me the whole code?

[Deductedsolid]

Dim x As Range
a = Worksheets("Work Order Tracking Form").Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To a

If Worksheets("Work Order Tracking Form").Cells(i, 8).Value = "Complete" Then
    If x Is Nothing Then
        Set x = Worksheets("Work Order Tracking Form").Cells(i, 8)
    Else
        Set x = Union(x, Worksheets("Work Order Tracking Form").Cells(i, 8))
    End If
End If
Next
If Not x Is Nothing Then
    x.EntireRow.Copy Worksheets("Completed 2020").Cells(Rows.Count, 1).End(xlUp)(2)
    x.EntireRow.Delete
End If
Application.CutCopyMode = False

ThisWorkbook.Worksheets("Work Order Tracking Form").Cells(1, 1).Select

[jindon]

And what kind of error are you getting?

[Deductedsolid]

x.EntireRow.Delete

it turns yellow and says delete method of range class failed

[jindon]

Then I need to see your workbook, as it is working here.

[jindon]

Or is the worksheet protected?

[Deductedsolid]

I ran my first macro and manually deleted columns then changed to your macro and yes it works. Thank you jindon