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
--
Gary's Student


"sverre" wrote:

> 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

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:

> 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
> --
> Gary's Student
>
>
> "sverre" wrote:
>
> > 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

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:

> 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:
>
> > 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
> > --
> > Gary's Student
> >
> >
> > "sverre" wrote:
> >
> > > 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

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 *
--
Gary's Student


"sverre" wrote:

> 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:
>
> > 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
> > --
> > Gary's Student
> >
> >
> > "sverre" wrote:
> >
> > > 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