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