View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Cecilkumara Fernando[_2_] Cecilkumara Fernando[_2_] is offline
external usenet poster
 
Posts: 93
Default 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