Hi,
following macro published by ron de bruin deletes all rows in a table where a cell contains a specific value in a given column.
The specific values are contained in an array likeBut in fact, the number of elements of the array could increase in time.![]()
myStrings = Array("Q8", "SUPP")
So I would like to adapt the macro so that it deletes all rows NOT containing given values ( they wil not vary in time)
As I am completely novice in VBA, I would appreciate your help very much
![]()
Sub Find_Example() Dim calcmode As Long Dim ViewMode As Long Dim myStrings As Variant Dim FoundCell As Range Dim I As Long Dim myRng As Range Dim sh As Worksheet With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can also use Sheets("MySheet") Set sh = Sheets("A") 'We search in column A in this example Set myRng = sh.Range("AJ:AJ") 'Add more search strings if you need myStrings = Array("Q8", "SUPP") With sh 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'We will search the values in MyRng in this example With myRng For I = LBound(myStrings) To UBound(myStrings) Do Set FoundCell = myRng.Find(What:=myStrings(I), _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) 'Use xlPart If you want to search in a part of the FoundCell 'If you use LookIn:=xlValues it will also delete rows with a 'formula that evaluates to "Ron" If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If Loop Next I End With End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = calcmode End With End Sub
Thanks in advance
André
Bookmarks