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

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

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

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

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

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

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

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

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

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

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

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

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

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