I'm confused at where the names are?

Are they in a single column? Are the names in a cell with nothing else in that
cell?

Or are the names scattered in any of the cells in that row? And can the names
be in cells that contain other stuff, too?

I'm guessing that the names are in a single column (K) and nothing else is in
that cell.

Option Explicit
Sub testme()

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

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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

myArray = Array("dubanj", "philarb")

With ActiveSheet
.DisplayPageBreaks = False
Firstrow = 1
Lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
For Lrow = Lastrow To Firstrow Step -1
If IsNumeric(Application.Match(.Cells(Lrow, "K"), myArray, 0)) Then
'it's on the list, so keep it
Else
'not on the list, so delete the row
.Rows(Lrow).Delete
End If
Next Lrow
End With

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

Marcusdmc wrote:

I'm trying to create a macro that will check an Array for a list of
names that should be salvaged and delete any other row that doesn't
contain a name in the array. It only keeps one row that has the name
and not the other rows whenever I try the following:

Dim myArray() As Variant
myArray() = Array("dubanj", "philarb")
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Columns("K:K").Select
Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If Application.WorksheetFunction.CountIf(.Rows(Lrow),
thismyArray) = 0 Then .Rows(Lrow).Delete
'delete the row if that row doesn't contain a name in the
array

Next
End With

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

Any Ideas as to what i'm doing wrong?? Thanks!

-Marcus

--

Dave Peterson