Delete Rows using an Array

[Crocdundee]

Hi Excel Gurus, I have found an example piece of code which could be of use to me.
However it does not work, and I was hoping you could fix it for me.
I am using 2007
The sheet is named Sheet1
The array data comes from A2:A
I wish to delete rows containing the names in the Array
Regards
Graham

Sub Example1()

Dim varList As Variant
Dim lngarrCounter As Long
Dim rngFound As Range, rngToDelete As Range
Dim strFirstAddress As String

Application.ScreenUpdating = False

varList = VBA.Array("Warwick", "Great Yarmouth", "Southwell", "Clairwood", "Turffontein Standside", "Sedgefield", "Beverley", "Sha Tin")

For lngarrCounter = LBound(varList) To UBound(varList)
With Sheet1.UsedRange
Set rngFound = .Find( _
What:=varList(lngarrCounter), _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address

If rngToDelete Is Nothing Then
Set rngToDelete = rngFound
Else
If Application.Intersect(rngToDelete, rngFound.EntireRow) Is Nothing Then
Set rngToDelete = Application.Union(rngToDelete, rngFound)
End If
End If

Set rngFound = .FindNext(After:=rngFound)

Do Until rngFound.Address = strFirstAddress
If Application.Intersect(rngToDelete, rngFound.EntireRow) Is Nothing Then
Set rngToDelete = Application.Union(rngToDelete, rngFound)
End If
Set rngFound = .FindNext(After:=rngFound)
Loop
End If
End With
Next lngarrCounter

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

Application.ScreenUpdating = True

End Sub

 

[shg]

If the values are in A2:Axx on Sheet1, and the rows to be deleted are on Sheet1, why not just delete rows 2:xx?

 

[Crocdundee]

Sorry was a typo, the data to check is in column A

rows start at A2:a

Does that help you to understand what is required ?
Thank a lot for your help

Graham

 

[Rick Rothstein]

Assuming the entries in Column A are text constants and not formulas, give this code a try (it will be much faster than the code structure you posted)...

Sub DeleteRows()
  Dim SearchRange As Range, V As Variant, VarList As Variant
  Set SearchRange = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
  VarList = VBA.Array("Warwick", "Great Yarmouth", "Southwell", "Clairwood", "Turffontein Standside", "Sedgefield", "Beverley", "Sha Tin")
  Application.ScreenUpdating = False
  For Each V In VarList
    SearchRange.Replace V, "=" & V, xlWhole, MatchCase:=False
  Next
  On Error Resume Next
  SearchRange.SpecialCells(xlCellTypeFormulas).EntireRow.Delete
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[Crocdundee]

Rick, you the Man.....Works perfectly
and I really appreciate it

Graham

 

[cjvenables]

@Rick Rothstein...

How would you change the code to delete words NOT contained in the array?

Thanks!

 

[Rick Rothstein]

@Rick Rothstein...

How would you change the code to delete words NOT contained in the array?

I think this modification to the code I posted earlier should do that...

Sub DeleteRows()
  Dim SearchRange As Range, V As Variant, VarList As Variant
  Set SearchRange = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
  VarList = VBA.Array("Warwick", "Great Yarmouth", "Southwell", "Clairwood", "Turffontein Standside", "Sedgefield", "Beverley", "Sha Tin")
  Application.ScreenUpdating = False
  For Each V In VarList
    SearchRange.Replace V, "=" & V, xlWhole, MatchCase:=False
  Next
  On Error Resume Next
  SearchRange.SpecialCells(xlCellTypeConstants).EntireRow.Delete
  On Error GoTo 0
  SearchRange.Replace "=", "", xlPart
  Application.ScreenUpdating = True
End Sub

 

[cjvenables]

Rick,

Works perfect, except it does not delete rows where the cells have nothing in them. I changed the array to a number array and am looking up values in column H (vendor numbers). It deleted all rows that had numbers that were not in the array, but it did not delete all rows where the cell in column H was blank.

I'm sure it's a simple fix. Thanks!

 

[Rick Rothstein]

Rick,

Works perfect, except it does not delete rows where the cells have nothing in them. I changed the array to a number array and am looking up values in column H (vendor numbers). It deleted all rows that had numbers that were not in the array, but it did not delete all rows where the cell in column H was blank.

I'm sure it's a simple fix. Thanks!

Untested, but I think if you add this statement after the existing EntireRow.Delete statement, it will do what you want...

SearchRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete