I have just one thing that is stumping me. I have modified your code to go
accross multiple columns but there are certain columns that I get a Run Time
Error 13, Type Mismatch error on. I have column A through K, which are just
general numbers, about every other column has up to 2 decimal places. Those
columns with decimals are where I am getting the run time error. If I do not
incorporate those columns, then the macro runs perfectly.

"Ron de Bruin" wrote:

You can use advanced filter but then you must enter the four values into the worksheet
This example loop through the cells in D1:D1000 and copy to a new sheet

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ws1
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = StartRow To EndRow Step 1

If IsError(.Cells(Lrow, "D").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "D").Value = "1" Or _
.Cells(Lrow, "D").Value = "2" Or _
.Cells(Lrow, "D").Value = "3" Or _
.Cells(Lrow, "D").Value = "4" Then .Rows(Lrow).Copy _
ws2.Cells(LastRow(ws2) + 1, 1)
End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Coal Miner" wrote in message ...
Ron,

Not sure? In the rows I wish to delete, the value in Column D is either
blank or contains some text string (the text string varies).


"Ron de Bruin" wrote:

Hi Coal Miner

Can we use <=4 ?

Another option is to use EasyFilter to do this
http://www.rondebruin.nl/easyfilter.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Coal Miner" wrote in message ...
Ron,

Similar situation - Rather than deleting rows based on specific criteria, I
would like to keep rows based on certain criteria and copy them to a new
worksheet with the new worksheet name to be the date (e.g. 3-9-06). I would
like to keep and copy all rows whose value in Column D is either 1,2,3, or 4.
Any guidance in accomplishing this would be appreciated. Thanks.

"Ron de Bruin" wrote:

Hi mathew

My range is A11: AD1656

You mean AD11: AD1656 I think ?

You can change this line
StartRow = 1
StartRow = 11

You can send me a small workbook private with the problem
I will look at it this evening


--
Regards Ron de Bruin
http://www.rondebruin.nl


"mathew" wrote in message ...
Ron: It did not work! When I run the VB in break mode it starts deleting the
rows, but when I just run the Macro excel freezes up and stops responding.
Can you heklp me? Below is the code. My range is A11: AD1656 which can get
larger! The endrow portion works it works backward as you set it up and
deletes rows as it should. I was worried that I had a sum formula in column
AD was the issue, but that appears to not be the case. Any suggestions??
Thank you!

Sub delete_row_using_Column_AD()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "AD").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "AD").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "AD").Value = 0 Then .Rows(Lrow).Delete
'This will delete each row with the Value 0 in Column AD,
case sensitive.

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

"Ron de Bruin" wrote:

Hi

You can use the loop
ElseIf .Cells(Lrow, "A").Value = 0

Or use AutoFilter (faster)
http://www.rondebruin.nl/delete.htm#AutoFilter

with DeleteValue = 0



--
Regards Ron de Bruin
http://www.rondebruin.nl


"mathew" wrote in message ...
Ron: Can you help me modify this?? I have a similar issue to Jeff's. I was
searching the forum for an answer and came upon this one. I'm trying to use
this to delete a row if the Column AD is equal to 0. How would I modify this
macro you have written? Thanks for your help!

"Jeff Bertram" wrote:

I need to delete entire rows if certain criteria exist. I used Ron de Bruin's macro (great macro Ron, thanks) and it
worked
fine.
The problem is, I receive multiple downloads usually about 10,000 rows long each. I do not want to go in and change the
code
for
each different download, as I would probably hose it up. I tried to enter the code in VB to have input boxes for what
column
to
look in and what to look for, but I am not that versed in VB. Can someone get me started in the right direction? Below
is
the
code from Ron de Bruin.

Sub Delete_row()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value = "ron" Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A, case sensitive.

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub