On Mar 7, 7:53*pm, "Rick Rothstein"
wrote:
I have a spreadsheet with copious amounts of data (10 sheets
with ~80 columns x 8000 rows). * The file size is so large that
I need to break the data into groups and then delete the rows
that do not pertain.

I have identified the rows I would like to delete by writing a
formula in Column A. This formula returns the word "DELETE"
for the rows I want to delete.

Since you already have formulas in place in Column A displaying either the
word DELETE or the empty string, then give this macro a 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"
* 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(1, UnusedCol), WS.Cells(LastRow, UnusedCol))
* * * .Value = WS.Range(WS.Cells(1, "A"), WS.Cells(LastRow, "A"))..Value
* * * .SpecialCells(xlCellTypeConstants).EntireRow.Delet e
* * End With
* Next
* On Error GoTo 0
* Application.Calculation = xlCalculationAutomatic
* Application.ScreenUpdating = True
End Sub

The only thing you need to do is change the cells being assigned to the
WSnames constant (the Const statement) to the names of the sheets you want
to run the code against. NOTE... do NOT put any spaces around the commas in
the list you create for this assignment.

Rick Rothstein (MVP - Excel)

Hi Rick,
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.