Find a text
 

Hi all
I am trying to find a text and delate a Row if the text is in the row.
But I am not sure how to do it.
Can someone help me?

Option Explicit
Sub FindText()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Cell = "Samtals hreyfing:" Then
€šthe text Samtals hreyfing sin in in the column E:E
€šIf the text Samtals hreyfing: is in the row then I want to delete the Row
End If
Next Cell
End Sub

Find a text
 

I think you may be looking for this...

Sub FindText()
Dim Cell As Range
For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E"))
If InStr(1, Cell.Value, "Samtals hreyfing:", vbTextCompare) Then
Cell.EntireRow.Delete
End If
Next Cell
End Sub

--
Rick (MVP - Excel)


"Geir" wrote in message
...
Hi all
I am trying to find a text and delate a Row if the text is in the row.
But I am not sure how to do it.
Can someone help me?

Option Explicit
Sub FindText()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Cell = "Samtals hreyfing:" Then
€šthe text Samtals hreyfing sin in in the column E:E
€šIf the text Samtals hreyfing: is in the row then I want to delete the Row
End If
Next Cell
End Sub

Find a text
 

You want to delete all the rows that have that string in it:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1") 'change this!

With wks.Range("E:e")
Do
Set FoundCell = .Cells.Find(what:="Samtals hreyfing:", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'no more left, get out of the loop
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End With
End Sub

Using Edit|Find is usually quicker than looping through all the cells.


Geir wrote:

Hi all
I am trying to find a text and delate a Row if the text is in the row.
But I am not sure how to do it.
Can someone help me?

Option Explicit
Sub FindText()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Cell = "Samtals hreyfing:" Then
€šthe text Samtals hreyfing sin in in the column E:E
€šIf the text Samtals hreyfing: is in the row then I want to delete the Row
End If
Next Cell
End Sub

--

Dave Peterson

Find a text
 

This may miss consecutive rows with that value in it. If I were looping through
the range, I'd either start at the bottom and work my way toward the top.

Or build a range to be deleted and delete it after all cells are compared.



Rick Rothstein wrote:

I think you may be looking for this...

Sub FindText()
Dim Cell As Range
For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E"))
If InStr(1, Cell.Value, "Samtals hreyfing:", vbTextCompare) Then
Cell.EntireRow.Delete
End If
Next Cell
End Sub

--
Rick (MVP - Excel)

"Geir" wrote in message
...
Hi all
I am trying to find a text and delate a Row if the text is in the row.
But I am not sure how to do it.
Can someone help me?

Option Explicit
Sub FindText()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Cell = "Samtals hreyfing:" Then
€šthe text Samtals hreyfing sin in in the column E:E
€šIf the text Samtals hreyfing: is in the row then I want to delete the Row
End If
Next Cell
End Sub


--

Dave Peterson

Find a text
 

To Dave: Aw crap! Of course you are right... I just went too fast in trying
to modify what the OP posted. Thanks for catching this!

To Geir: Use Dave's code... it will be faster than any loop that looks at
all individual cells in the range.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
This may miss consecutive rows with that value in it. If I were looping
through
the range, I'd either start at the bottom and work my way toward the top.

Or build a range to be deleted and delete it after all cells are compared.



Rick Rothstein wrote:

I think you may be looking for this...

Sub FindText()
Dim Cell As Range
For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E"))
If InStr(1, Cell.Value, "Samtals hreyfing:", vbTextCompare) Then
Cell.EntireRow.Delete
End If
Next Cell
End Sub

--
Rick (MVP - Excel)

"Geir" wrote in message
...
Hi all
I am trying to find a text and delate a Row if the text is in the row.
But I am not sure how to do it.
Can someone help me?

Option Explicit
Sub FindText()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Cell = "Samtals hreyfing:" Then
?sthe text Samtals hreyfing sin in in the column E:E
?sIf the text Samtals hreyfing: is in the row then I want to delete
the Row
End If
Next Cell
End Sub


--

Dave Peterson