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

Glad you got it figured out!

Marcusdmc wrote:

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

--

Dave Peterson

Is it possible to have blank spaces in your Array? Doesn't seem to be
working when I use blank spaces. i.e.

myArray("smith - aaron", "johnson - sara")



-Marcus

Yes.

Any chance that you have more than one space in the real data--or extra
leading/trailing spaces???

Or even those non-breaking HTML spaces (chr(160)'s)???

Marcusdmc wrote:

Is it possible to have blank spaces in your Array? Doesn't seem to be
working when I use blank spaces. i.e.

myArray("smith - aaron", "johnson - sara")

-Marcus

--

Dave Peterson

Yes there would be more than one space in some instances in the data,
but no leading spaces or trailing, just in between the beginning and
the end... for instance: "Ar Pro Nw" or "Ma Pro"
Hope that makes sense.

-Marcus

I meant would there be multiple consecutive spaces.

Ar Pro Nw
is different
Ar Pro Nw



Marcusdmc wrote:

Yes there would be more than one space in some instances in the data,
but no leading spaces or trailing, just in between the beginning and
the end... for instance: "Ar Pro Nw" or "Ma Pro"
Hope that makes sense.

-Marcus

--

Dave Peterson

ps.

And if I had this situation, I'd spend time cleaning up the data. I may be able
to work around it in this specific routine, but I may not remember to do the
same fix on the next one.

I think it's always better to clean up the data as close to the original source
as possible.

Dave Peterson wrote:

I meant would there be multiple consecutive spaces.

Ar Pro Nw
is different
Ar Pro Nw

Marcusdmc wrote:

Yes there would be more than one space in some instances in the data,
but no leading spaces or trailing, just in between the beginning and
the end... for instance: "Ar Pro Nw" or "Ma Pro"
Hope that makes sense.

-Marcus

--

Dave Peterson

--

Dave Peterson

Ahh sorry I understand. No, only one space... between sets of
characters, only one space like:
Ar Pro Nw
not:
Ar Pro Nw


There wouldn't be a time when there would be multiple spaces between
characters as in: "Ar Pro Nw"

-Marcus

Pick out a cell that you "know" matches one of the non-matching entries (Say
K88).

Then put this in an empty cell:
=K88="Ar Pro Nw"
(match the address and the string, though)

Do you see True or False?

If you see False, then there is a difference.


Marcusdmc wrote:

Ahh sorry I understand. No, only one space... between sets of
characters, only one space like:
Ar Pro Nw
not:
Ar Pro Nw

There wouldn't be a time when there would be multiple spaces between
characters as in: "Ar Pro Nw"

-Marcus

--

Dave Peterson

Ahh thank you! :) It worked, I had changed my process to instead of
deleting the whole row to clearing the cell, but i wrote the syntax
incorrectly. made it
..Cells(IRow, "K").ClearContents

so it works now.

Thanks yet again,

-Marcus