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

I was trying to rearrange the outcome of the if then else statement to
turn this around and actually delete the rows in the array as opposed
to deleting them, but I am coming up with nothing happening when I try
this, am I going about it the wrong way?

Dim myArray As Variant
Dim Firstrow 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("TotalTasks", "adkinj10", "henderk6", "brownm33",
"burnsd13", "deverea", "devitof", "englank", "evansd15", "hagertm",
"heltonm", "jonesb36", "matthie1", "murphj22", "petrica", "philharb",
"shawd2", "shermab5", "sullivd", "thomac30", "walkdt")


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 delete it
.Rows(Lrow).Delete
Else
'not on the list, so keep the row
End If
Next Lrow
End With


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


-Marcus

I was trying to rearrange the outcome of the if then else statement
to
turn this around and actually delete the rows in the array as opposed
to deleting them, but I am coming up with nothing happening when I
try
this, am I going about it the wrong way?

Dim myArray As Variant
Dim Firstrow 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("TotalTasks", "philharb", "durbanj")


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 delete it
.Rows(Lrow).Delete
Else
'not on the list, so keep the row
End If
Next Lrow
End With


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


-Marcus

I'm kind of confused.

Do you mean you want to delete the row if it doesn't match any name in that
array? (Or keep it if the name matches one of the names in the array)

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 KeepIt As Boolean

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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView


myArray = Array("TotalTasks", "adkinj10", "henderk6", _
"brownm33", "burnsd13", "deverea", _
"devitof", "englank", "evansd15", _
"hagertm", "heltonm", "jonesb36", _
"matthie1", "murphj22", "petrica", _
"philharb", "shawd2", "shermab5", _
"sullivd", "thomac30", "walkdt")

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
KeepIt = False
If IsNumeric(Application.Match(.Cells(lRow, "K"), myArray, 0)) _
Or IsNumeric(Application.Match(.Cells(lRow, "B"), myArray, 0)) _
Then
KeepIt = True
'stop looking for more matches
Exit For
End If
If KeepIt = True Then
'do nothing, it's on the list (somewhere)
Else
'it's not the list, so delete it
.Rows(lRow).Delete
End If
Next lRow
End With

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

End Sub


(Compiled, but untested.)

Marcusdmc wrote:
>
> I was trying to rearrange the outcome of the if then else statement to
> turn this around and actually delete the rows in the array as opposed
> to deleting them, but I am coming up with nothing happening when I try
> this, am I going about it the wrong way?
>
> Dim myArray As Variant
> Dim Firstrow 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("TotalTasks", "adkinj10", "henderk6", "brownm33",
> "burnsd13", "deverea", "devitof", "englank", "evansd15", "hagertm",
> "heltonm", "jonesb36", "matthie1", "murphj22", "petrica", "philharb",
> "shawd2", "shermab5", "sullivd", "thomac30", "walkdt")
>
> 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 delete it
> .Rows(Lrow).Delete
> Else
> 'not on the list, so keep the row
> End If
> Next Lrow
> End With
>
> ActiveWindow.View = ViewMode
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
>
> -Marcus

--

Dave Peterson

Sorry for the confusion! I was wanting it to delete a whole row in
where these values in the array showed up in column B or column K, but
it turns out it does work correctly, I just wasn't looking at the
right set of results after running the macro! Thank you tons for your
help!

-Marcus