Delete Rows

[Dominique Feteau]

I found this macro to delete a row that contained the word "dividend". How
can I change it so it will delete any rows that have "0" in Column Q. I
also dont want to have this as its own macro, but to insert it into another
macro I created already. How would I do that?

Public Sub DeleteRowsWithWord()
Const sWORD As String = "Dividend"
Dim found As Range

Application.ScreenUpdating = False
Set found = ActiveSheet.Cells.Find( _
What:=sWORD, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not found Is Nothing Then
Do
found.EntireRow.Delete
Set found = ActiveSheet.Cells.FindNext
Loop Until found Is Nothing
End If
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi

I have some examples on this page
http://www.rondebruin.nl/delete.htm

Try this one

Sub FindExample1()
Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("0")

'myArr = Array("1", "3", "5")
'You can also use more then one value in the Array

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("Q:Q").Find(What:=myArr(I), After:=Range("Q" _
& Rows.Count), LookAt:=xlWhole)
'If you want to search in a part of the rng.value then use xlPart
If Not Rng Is Nothing Then Rng.EntireRow.delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True
End Sub

 

[Dominique Feteau]

thanx. that worked like a charm.

Niq

Hi

I have some examples on this page
http://www.rondebruin.nl/delete.htm

Try this one

Sub FindExample1()
Dim myArr As Variant
Dim Rng As Range
Dim I As Long

Application.ScreenUpdating = False
myArr = Array("0")

'myArr = Array("1", "3", "5")
'You can also use more then one value in the Array

For I = LBound(myArr) To UBound(myArr)

Do
Set Rng = Range("Q:Q").Find(What:=myArr(I), After:=Range("Q" _
& Rows.Count), LookAt:=xlWhole)
'If you want to search in a part of the rng.value then use xlPart
If Not Rng Is Nothing Then Rng.EntireRow.delete
Loop While Not (Rng Is Nothing)

Next I
Application.ScreenUpdating = True
End Sub