PDA

View Full Version : vba to cut row to different sheet



msuaccount
08-02-2011, 09:29 AM
I need a VBA code that will cut rows from one worksheet that return a #N/A after running a vlookup. I have a code that deletes the rows but I need to keep these rows for future references. The current code I have is.

Sub ChequeRemove()
Dim wb As Workbook, oWS As Worksheet, nWS As Worksheet
Dim i As Long, LastRow As Long, LastCol As Long, nLastRow As Long
Set wb = ActiveWorkbook
Set oWS = wb.Sheets("Past One Year")
Set nWS = wb.Sheets("New")

nLastRow = nWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastRow = oWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastCol = oWS.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Column


For i = LastRow To 1 Step -1
If oWS.Cells(i, (LastCol)).Text = "Cashed" Then Rows(i).EntireRow.Delete
Next i
End Sub