I was graciously provided a macro that deletes all rows not having certain
values in col A. In the following line:

ArrNames = Array("Agt", "Agent")

Is it possible to add a wildcard to "Agt"? Those are just the first 3
letters of the lines I'm looking for and are followed by more text. None of
the rows are unique and are being deleted as a result of the routine.

I have pasted the provided code below, should the above make no sense.

Thank you for your help!!

Ken


Sub Loop_Example()

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim ArrNames As Variant

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = .UsedRange.Cells(7).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

ArrNames = Array(" Agt ID", "Agent Summary")

For Lrow = Lastrow To Firstrow Step -1

With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If IsError(Application.Match(.Value, ArrNames, 0)) Then
..EntireRow.Delete

End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

I believe you could use the LIKE operator to do this.

If YourString like "ABC*" 'Finds cells that start with ABC.

If YourString like "*ABC*" 'Finds cells with ABC anywhere in them
--
HTH,
Barb Reinhardt



"fpd833" wrote:

> I was graciously provided a macro that deletes all rows not having certain
> values in col A. In the following line:
>
> ArrNames = Array("Agt", "Agent")
>
> Is it possible to add a wildcard to "Agt"? Those are just the first 3
> letters of the lines I'm looking for and are followed by more text. None of
> the rows are unique and are being deleted as a result of the routine.
>
> I have pasted the provided code below, should the above make no sense.
>
> Thank you for your help!!
>
> Ken
>
>
> Sub Loop_Example()
>
> Dim Firstrow As Long
> Dim Lastrow As Long
> Dim Lrow As Long
> Dim CalcMode As Long
> Dim ViewMode As Long
> Dim ArrNames As Variant
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> With ActiveSheet
> .Select
>
> ViewMode = ActiveWindow.View
> ActiveWindow.View = xlNormalView
>
> .DisplayPageBreaks = False
>
> Firstrow = .UsedRange.Cells(7).Row
> Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
>
> ArrNames = Array(" Agt ID", "Agent Summary")
>
> For Lrow = Lastrow To Firstrow Step -1
>
> With .Cells(Lrow, "A")
>
> If Not IsError(.Value) Then
>
> If IsError(Application.Match(.Value, ArrNames, 0)) Then
> .EntireRow.Delete
>
> End If
>
> End With
>
> Next Lrow
>
> End With
>
> ActiveWindow.View = ViewMode
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
>
> End Sub
>

Thanks Barb! How/where would I work that into the code?

"Barb Reinhardt" wrote:

> I believe you could use the LIKE operator to do this.
>
> If YourString like "ABC*" 'Finds cells that start with ABC.
>
> If YourString like "*ABC*" 'Finds cells with ABC anywhere in them
> --
> HTH,
> Barb Reinhardt
>
>
>
> "fpd833" wrote:
>
> > I was graciously provided a macro that deletes all rows not having certain
> > values in col A. In the following line:
> >
> > ArrNames = Array("Agt", "Agent")
> >
> > Is it possible to add a wildcard to "Agt"? Those are just the first 3
> > letters of the lines I'm looking for and are followed by more text. None of
> > the rows are unique and are being deleted as a result of the routine.
> >
> > I have pasted the provided code below, should the above make no sense.
> >
> > Thank you for your help!!
> >
> > Ken
> >
> >
> > Sub Loop_Example()
> >
> > Dim Firstrow As Long
> > Dim Lastrow As Long
> > Dim Lrow As Long
> > Dim CalcMode As Long
> > Dim ViewMode As Long
> > Dim ArrNames As Variant
> >
> > With Application
> > CalcMode = .Calculation
> > .Calculation = xlCalculationManual
> > .ScreenUpdating = False
> > End With
> >
> > With ActiveSheet
> > .Select
> >
> > ViewMode = ActiveWindow.View
> > ActiveWindow.View = xlNormalView
> >
> > .DisplayPageBreaks = False
> >
> > Firstrow = .UsedRange.Cells(7).Row
> > Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
> >
> > ArrNames = Array(" Agt ID", "Agent Summary")
> >
> > For Lrow = Lastrow To Firstrow Step -1
> >
> > With .Cells(Lrow, "A")
> >
> > If Not IsError(.Value) Then
> >
> > If IsError(Application.Match(.Value, ArrNames, 0)) Then
> > .EntireRow.Delete
> >
> > End If
> >
> > End With
> >
> > Next Lrow
> >
> > End With
> >
> > ActiveWindow.View = ViewMode
> > With Application
> > .ScreenUpdating = True
> > .Calculation = CalcMode
> > End With
> >
> > End Sub
> >