M
Marcusdmc
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
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