For a search routine you should look to use Find, AutoFilter or variant array approaches. Range loops are nomally too slow, worse again if they use Select
The code below will look for the strText variable in a user selected range, it then adds any matches to a range variable rng2 which you can then further process
Option Explicit
Const strText As String = "%"
Sub ColSearch_DelRows()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim cel1 As Range
Dim cel2 As Range
Dim strFirstAddress As String
Dim lAppCalc As Long
'Get working range from user
On Error Resume Next
Set rng1 = Application.InputBox("Please select range to search for " & strText, "User range selection", Selection.Address(0, 0), , , , , 8)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
With Application
lAppCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set cel1 = rng1.Find(strText, , xlValues, xlPart, xlByRows, , False)
'A range variable - rng2 - is used to store the range of cells that contain the string being searched for
If Not cel1 Is Nothing Then
Set rng2 = cel1
strFirstAddress = cel1.Address
Do
Set cel1 = rng1.FindNext(cel1)
Set rng2 = Union(rng2, cel1)
Loop While strFirstAddress <> cel1.Address
End If
If Not rng2 Is Nothing Then
For Each cel2 In rng2
Debug.Print cel2.Address & " contained " & strText
Next
Else
MsgBox "No " & strText
End If
With Application
.ScreenUpdating = True
.Calculation = lAppCalc
End With
End Sub