Variable criteria to delete rows
 

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

Variable criteria to delete rows
 

Hi Jeff

This example will ask your for the criteria and it will
look in the Activecell column

Sub Delete_row_test()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim findstring As String

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

findstring = InputBox("Enter a Search value")
If Trim(findstring) < "" Then

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

ElseIf .Cells(Lrow, ActiveCell.Column).Value = findstring Then .Rows(Lrow).Delete

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


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


"Jeff Bertram" <Jeff wrote in message ...
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

Variable criteria to delete rows
 

Thanks, Ron. you have truly made my life easier. Thanks again!!!!

"Ron de Bruin" wrote:

Hi Jeff

This example will ask your for the criteria and it will
look in the Activecell column

Sub Delete_row_test()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim findstring As String

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

findstring = InputBox("Enter a Search value")
If Trim(findstring) < "" Then

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

ElseIf .Cells(Lrow, ActiveCell.Column).Value = findstring Then .Rows(Lrow).Delete

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


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


"Jeff Bertram" <Jeff wrote in message ...
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

Variable criteria to delete rows
 

Thank you so much Ron. You have truly made my life easier. Thanks again!!!!

"Ron de Bruin" wrote:

Hi Jeff

This example will ask your for the criteria and it will
look in the Activecell column

Sub Delete_row_test()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim findstring As String

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

findstring = InputBox("Enter a Search value")
If Trim(findstring) < "" Then

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

ElseIf .Cells(Lrow, ActiveCell.Column).Value = findstring Then .Rows(Lrow).Delete

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


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


"Jeff Bertram" <Jeff wrote in message ...
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

Variable criteria to delete rows
 

Ron,
What is the significant of having
.DisplayPageBreaks = False
in the code.
Regards,
Cecil

"Ron de Bruin" wrote in message
...
Hi Jeff

This example will ask your for the criteria and it will
look in the Activecell column

Sub Delete_row_test()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim findstring As String

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

findstring = InputBox("Enter a Search value")
If Trim(findstring) < "" Then

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

ElseIf .Cells(Lrow, ActiveCell.Column).Value = findstring
Then .Rows(Lrow).Delete

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


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


"Jeff Bertram" <Jeff wrote in message
...
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

Variable criteria to delete rows
 

Hi Cecil

It will slow down the macro because it must change the
position of the pagebreaks after each row you delete.

That's why we set it to false

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


"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message ...
Ron,
What is the significant of having
.DisplayPageBreaks = False
in the code.
Regards,
Cecil

"Ron de Bruin" wrote in message
...
Hi Jeff

This example will ask your for the criteria and it will
look in the Activecell column

Sub Delete_row_test()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim findstring As String

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

findstring = InputBox("Enter a Search value")
If Trim(findstring) < "" Then

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

ElseIf .Cells(Lrow, ActiveCell.Column).Value = findstring
Then .Rows(Lrow).Delete

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


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


"Jeff Bertram" <Jeff wrote in message
...
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

Variable criteria to delete rows
 

Enlightened!
Thank you,
Cecil

"Ron de Bruin" wrote in message
...
Hi Cecil

It will slow down the macro because it must change the
position of the pagebreaks after each row you delete.

That's why we set it to false

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


"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
Ron,
What is the significant of having
.DisplayPageBreaks = False
in the code.
Regards,
Cecil

"Ron de Bruin" wrote in message
...
Hi Jeff

This example will ask your for the criteria and it will
look in the Activecell column

Sub Delete_row_test()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim findstring As String

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

findstring = InputBox("Enter a Search value")
If Trim(findstring) < "" Then

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

ElseIf .Cells(Lrow, ActiveCell.Column).Value =
findstring
Then .Rows(Lrow).Delete

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


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


"Jeff Bertram" <Jeff wrote in
message
...
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

Variable criteria to delete rows
 

Ron: Can you help me modify this?? I have a similar issue to Jeffs. I was
searching the forum for an answer and came upon this one. Im 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

Variable criteria to delete rows
 

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

Variable criteria to delete rows
 

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

Variable criteria to delete rows
 

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

Variable criteria to delete rows
 

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

Variable criteria to delete rows
 

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

Variable criteria to delete rows
 

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

Variable criteria to delete rows
 

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

Variable criteria to delete rows
 

Brilliant!! Thanks Ron!

"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

Thank you so much!!!
 

I have been fiddling around with formulas, templates, and now macros for
about the past month with my new job. Someone found out I was good with
computers and gave me a monstrous task to do. But anyways i have been
struggling with macros for a few days now to get it to do what i wanted. Ron
you are a god send, after an hour or 2 messing with your macro i finally
modified it to delete the rows in my list i no longer wanted. Thanks Again!!

"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

Thank you so much!!!
 

You are welcome Johnny

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Johnny" wrote in message ...
I have been fiddling around with formulas, templates, and now macros for
about the past month with my new job. Someone found out I was good with
computers and gave me a monstrous task to do. But anyways i have been
struggling with macros for a few days now to get it to do what i wanted. Ron
you are a god send, after an hour or 2 messing with your macro i finally
modified it to delete the rows in my list i no longer wanted. Thanks Again!!

"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

Variable criteria to delete rows
 

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