Hi everybody
i have found this code from here and it works perfect however when it is cut it from Sheet 1 to Sheet 2 with specific word "Completed" it cut all the rows that contains "Completed" on column "AG" however it then leave the blanks rows on sheet1 is there any way that when it is moved to sheet 2 from sheet1 it will continue to look for next row with the specific word and will also continue to delete each blank rows.
i have found this code from here and it works perfect however when it is cut it from Sheet 1 to Sheet 2 with specific word "Completed" it cut all the rows that contains "Completed" on column "AG" however it then leave the blanks rows on sheet1 is there any way that when it is moved to sheet 2 from sheet1 it will continue to look for next row with the specific word and will also continue to delete each blank rows.
VBA Code:
Option Explicit
Sub Test()
Dim sht1 As Worksheet, sht2 As Worksheet
Dim i As Long
Set sht1 = ThisWorkbook.Worksheets("Full")
Set sht2 = ThisWorkbook.Worksheets("Completed")
For i = 2 To sht1.Cells(sht1.Rows.Count, "AG").End(xlUp).Row
If sht1.Range("AG" & i).Value = "Completed" Then
sht1.Range("A" & i).EntireRow.Cut Destination:=sht2.Range("A" & sht2.Cells(sht2.Rows.Count, "AG").End(xlUp).Row + 1)
End If
Next i
End Sub