Selecting a cell with a value and deleting it along with 4 cells above

[kknb4591]

Hi,

I have a piece of code which searches for the phrase "Global" in a sheet and then deletes that cell and the 4 cells above it:

Cells.Find(What:="Global", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("A1625").Select
Range("A1621:A1625").Select
Range("A1625").Activate
Selection.ClearContents

However, how can I get it to automatically find the word delete the cell and then delete the 4 cells above? As the word "Global" will not always be in cell A1625. It will, however, always be in column A if that helps.

Would greatly appreciate assistance with this.

 

[Andrew Poulsom]

Try:

    Cells.Find(What:="Global", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-4).Resize(5).ClearContents

 

[kknb4591]

Thank you. Works a treat.

 

[kknb4591]

While this works well, I need to delete the row with the word Global in and also the 4 rows above.

How would I do this?

Thanks

 

[Andrew Poulsom]

Try:

    Cells.Find(What:="Global", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-4).Resize(5).EntireRow.Delete

 

[kknb4591]

Thanks very much again. That works perfectly.

I'm having trouble doing a general file reference as well.

After the piece of code you gave me need to insert:

ActiveSheet.Range("$A$1:$BV$3294").RemoveDuplicates Columns:=16, Header:= xlYes

But the range will always change. How would I select everything and remove duplicates based on the values in Col 16? I've tried a few things but nothing seems to work.

Thanks again for your time.

 

[Andrew Poulsom]

Try:

Dim LastRow As Long
With ActiveSheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A1:BV" & LastRow).RemoveDuplicates Columns:=16, Header:= xlYes
End With

 

[kknb4591]

Genius. Much appreciated