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

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

Thanks so much! That did the trick. Out of curiousity, if the names
were scattered, how would that be done?

Each name in its own cell? And I can still use column K to find the last row?

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
Dim nCtr As Long
Dim KeepIt As Boolean

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
'still use column K for to find the lastrow?????
Lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
For Lrow = Lastrow To Firstrow Step -1
KeepIt = False
'loop through the names in myArray
For nCtr = LBound(myArray) To UBound(myArray)
If Application.CountIf(.Rows(Lrow), myArray(nCtr)) 0 Then
KeepIt = True
Exit For 'no need to keep looking
End If
Next nCtr

If KeepIt = True Then
'keep it!
Else
.Rows(Lrow).Delete
End If
Next Lrow
End With

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

If the names could be in cells with other stuff, you could change this line:
If Application.CountIf(.Rows(Lrow), myArray(nCtr)) 0 Then
to
If Application.CountIf(.Rows(Lrow), "*" & myArray(nCtr) & "*") 0 Then

Marcusdmc wrote:

Thanks so much! That did the trick. Out of curiousity, if the names
were scattered, how would that be done?

--

Dave Peterson

Ahh, I should have been more clear, I'm sorry. Was curious if you
could look in 2 columns, say B and K, but yes, each name would be in
it's own cell, but thank you for the expression of how the other one
works too, that is very useful too.

-Marcus

I'd just look twice--once in column K and once in column B.

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
'still using column K to get the lastrow
Lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
For Lrow = Lastrow To Firstrow Step -1
If IsNumeric(Application.Match(.Cells(Lrow, "K"), myArray, 0)) _
Or IsNumeric(Application.Match(.Cells(Lrow, "B"), 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:

Ahh, I should have been more clear, I'm sorry. Was curious if you
could look in 2 columns, say B and K, but yes, each name would be in
it's own cell, but thank you for the expression of how the other one
works too, that is very useful too.

-Marcus

--

Dave Peterson