Thank you for the response. I am having some issues with
the macro. I changed the sheet names to the names of the
sheets in my workbook it does not seem to work. Could it
be my formula in column A? Here is the formula I am using.
=IFERROR(IF(VLOOKUP($B11,'MainList'!$C$28:$K$8242, 9,
FALSE)<$A$4,"DELETE",$A$4),"DELETE").
Cell A4 represents the "region" I want to keep. If cell A4's
value is SW, the formula will return either "SW" or "DELETE".
My data also starts in row 6 (rows 1-4 are headers) Does this
make a difference? Thank you again.

Yes, your formula is causing my code the problem. The way you first
described what your formula did, I got the impression it displayed either
the word DELETE or nothing at all, so I designed the code for that
interpretation... actually, something is displayed in every cell by your
formula. Given your fuller description, here is a different macro for you to
try...

Sub DeleteDELETEs()
Dim UnusedCol As Long, LastRow As Long, Cell As Range, WS As Worksheet
Const WSnames As String = "Sheet1,Sheet2,Sheet3,Sheet4,Sheet5"
Const StartRow As Long = 6
Const FormulaCol As String = "J"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
For Each WS In Worksheets(Split(WSnames, ","))
UnusedCol = WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
LastRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
With WS.Range(WS.Cells(StartRow, UnusedCol), WS.Cells(LastRow,
UnusedCol))
.Value = WS.Range(WS.Cells(StartRow, FormulaCol), WS.Cells(LastRow,
FormulaCol)).Value
.Replace "DELETE", "", xlWhole, , False
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
Columns(UnusedCol).Clear
Next
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)