Delete Cells If....

[gheyman]

I need to modify this code but I am a bit lost because I want to delete Cells (Z:AA) if the Value in Column AA = "NEW"

I have values down column Z starting in Z1. I have a formula in Column AA that tells me if the value in Z is something new or not. I want to reduce the list in column Z down to only new items, then take that reduced list and paste it to the bottom of column A

This is the code I have that deletes Row, but I am trying to modify it for the above

Dim LR As Long, i As Long, X As Variant
X = Sheets("Sheet1").Range("A1").Value
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("P" & i).Value <> X Then Rows(i).Delete
Next I

Thanks!!

 

[hippiehacker]

this will copy the cells Z:AA to Col A

With ActiveSheet
    .AutoFilterMode = False
        .Range("Z1:AA" & .Range("AA" & Rows.Count).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="new"
        .Range("Z1:AA" & .Range("AA" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1)
    .AutoFilterMode = False
End With

 

[gheyman]

Thank you so much. But its copying the very first set of cells (Z1:AA1) even if it doesn't say "New"

Also, I didn't need to copy column AA.

I really appreciate your help