Cut a row below the last row of a table

[jabudi11]

Hello I am trying to write vba maybe someone can help me with this task. Every day I receive a report and I need to cut the rows where column D says NYMTR and Column N says Rollovered and move it to two rows below the table...

Here is something that is not working for me:

Sub MoveRows()

FinalRow = Cells(1048576, 1).End(x1Up).Row
For x = 1 To FinalRow

    If Cells(x, "N").Value = "Rollovered" And Cells(x, "D").Value = "NYMTR" Then
        ActiveCell.EntireRow.Cut Destination:=FinalRow+2
      
    End If
    Next x

End Sub

[alansidman]

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

Posting code between 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



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

You say it is not working. What does that mean. Are you getting an error message? If so, what is the message and what line is highlighted when you debug? Or, is nothing happening? Please clarify.

[jabudi11]

The first row is being highlighted

FinalRow = Cells(1048576, 1).End(x1Up).Row

[jabudi11]

It said application defined error or object defined error

I got that line from an excel book though I don't know why it is causing an error.

[judgeh59]

it also matters what the error is....since you don't have FinalRow dimensioned it's probably a variant. If your file is an .xls then the problem could be that you are only allowed ~65000 rows. You could try setting the 1048576 to 65000 and see what that does...but you need to determine what the actually max rows will be.....

EDIT: Thanks for the error code....

[judgeh59]

FinalRow = Cells(1048576, 1).End(xlUp).Row

the 1 in x1Up should be the letter l (ell)

[jabudi11]

Thanks judge, that helps get rid of the error message. However, now when I try to run the code it does not make any changes and no errors come up.

[judgeh59]

have you stepped through the code to see what it is doing?....Is FinalRow getting set to something you think it should be set to?

[AB33]

If you want to delete or cut a row, you should look back ward

For x =  FinalRow to 1 step -1

.

If the code does nothing, it can only mean, this line is false

 If Cells(x, "N").Value = "Rollovered" And Cells(x, "D").Value = "NYMTR" Then

In other words, both conditions do not return true

Last use this line

rows(x).cut FinalRow+2

[jabudi11]

Right now I am getting an error that says cut method of range class failed.

Tha last line is highlighted:

Rows(x).Cut FinalRow + 2

Sub macro1()
FinalRow = Cells(1048576, 1).End(xlUp).Row

For x = FinalRow To 2 Step -1
If Cells(x, "E").Value = "Rollovered" And Cells(x, "C").Value = "Equity" Then
Rows(x).Cut FinalRow + 2

End If
Next x

End Sub

[AB33]

I am not surprised you got an error.
Where is FinalRow as you cut the rows?
You also have a table where the last row might not work.

You can try this line, but will come up with the same error

Cells(x, "A").EntireRow.Cut FinalRow + 2

You have probably complicated the issue by you are not using a filter function, or a different construction of loop.
Attach a sample and will write you a code.