Hello,
I am new in VB and im trying to do a macro that will allow me to leave each row that match certain values and delete all the other rows that dont have that value. I have a working script but as soon as i add more than 11 values VB will give me errors. I want to add more than 11 values but VB will not allow me to do. Any ideas of how i can do this?
This is the code im using, can someone help me please.
Sub DeleteCells()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
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 IsError(.Cells(Lrow, "A").Value) Then
'Avoid an error if there is an error in the cell
ElseIf Application.WorksheetFunction.CountIf(.Rows(Lrow), "Dale") = 0 And Application.WorksheetFunction.CountIf(.Rows(Lrow), "Kevin") = 0 Then .Rows(Lrow).Delete
'This will delete each row that hasn't got these values in the cell
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Thanks in advanced :-)
Kevin
Bookmarks