Delete Rows using an Array

Crocdundee

Board Regular
Joined
May 10, 2010
Messages
174
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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?
 
Upvote 0
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
 
Upvote 0
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)...
Code:
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
 
Upvote 0
Rick, you the Man.....Works perfectly
and I really appreciate it

Graham
 
Upvote 0
@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...

Code:
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
 
Upvote 0
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!
 
Upvote 0
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...

Code:
SearchRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top