delete rows that contain ********* ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have tried to delete rows that contain ******** in col no 3.
with below code. problem is that xl thinks it is wildcards and deletes
everything....
.. Solvable??

Worksheets("FRB").Select
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
Dim rng As Range
Columns(3).Replace What:="******** ", _
Replacement:="=na()", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
On Error Resume Next
Set rng = Columns(3).SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.delete
End If
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With

End Sub
 
Try:

Sub star_killer()
Dim r As Range, rr As Range
Set r = ActiveSheet.UsedRange
n = nLastRow + r.Rows.Count + r.Row - 1
For i = 1 To n
If Cells(i, 3).Value = "********" Then
If rr Is Nothing Then
Set rr = Cells(i, 3)
Else
Set rr = Union(rr, Cells(i, 3))
End If
End If
Next
If rr Is Nothing Then
Else
rr.EntireRow.Delete
End If
End Sub
 
Hi Gary

No, it does not work. The stars are not killed. Nothing is actually affected.
Do I send you the file so you can check the code?

Br Sverre

"Gary''s Student" skrev:
 
Hi Sverre

In my opinion both codes work fine.
but in your code there is a space behind the stars.
I don't know if you noticed that.

Piet


sverre schreef:
 
No need to send me the file. Someone else in the Center pointed out that I
must need new glasses. My code (erroneously) looked for 8 *s.

Your code looked for 8 *s followed by a single space. Try my code again
with a single space after the last *
 
Back
Top