vba Delete Row(s) not matching table criteria Macro

[NGB82KS]

So i'll do my best to try and explain this.
I have a spreadsheet with 2 tabs (DataExport; FilterCriteria)
The DataExport tab is every computer i pulled from active directory (real long list) Column A is the PC name and Column B is the OU it resides in.
The FilterCriteria tab Column A is a list of the PC names that I would be looking for.
Note* Our PC naming conventions have gone through some changes, so i need the search to look for anything that begins, ends or contains the computer name in Column A of the FilterCriteria tab.

I need to be able to execute a macro that Deletes all rows from the DataExport tab that doesn't match the name filters in Column A/Name table of the FilterCriteria tab. Any help would be greatly appreciated.

 

[NGB82KS]

Well now it give me the number it will delete, but says delete message of range class failed and highlights r.EntireRow.Delete

 

[NGB82KS]

Well now it give me the number it will delete, but says delete message of range class failed and highlights r.EntireRow.Delete

Im going to have to get you a beer.... this is ridiculous

 

[DanteAmor]

sorry I did not understand. does it work for you?

 

[NGB82KS]

sorry I did not understand. does it work for you?

No, When i run it, it now tells me the correct # of rows it will delete but when i press yes, it gives me the 'Delete method of Range class failed" Runtime 1004 error and highlights the r.EntireRow.Delete portion.

 

[DanteAmor]

What changed from your first test data to this test data? Do you have merged cells, the sheet is protected, something different?

 

[DanteAmor]

you can remove the table and pass the data to a normal range and try again

 

[NGB82KS]

Nope. I tried using fake data in a new spreadsheet with fake criteria, but the code stills give the error on the r.EntireRow.Delete.
If i change the r.Cells.Count back to r.Rows.Count it runs with no error, but my results are 0, which is incorrect. The .Cells shows the correct # to delete, but the last part to delete it is whats not happening.

 

[DanteAmor]

that's very weird, try the following

dim m
m = r.cells.count
vbAnswer = MsgBox(m - 1 & " Rows will be deleted. Do you want to continue?", vbYesNo, "Delete Rows Macro")
  If vbAnswer = vbYes Then r.EntireRow.Delete

 

[DanteAmor]

Or try this other alternative.

Sub Delete_Row_1()
  Dim arr As Variant, i As Long, j As Long, lr As Long
  Dim a As Variant, b As Variant, r As Range, counter As Long
  Dim sh1 As Worksheet, sh2 As Worksheet, exists As Boolean, vbAnswer As Variant
  
  Application.ScreenUpdating = False
  '
  Set sh1 = Sheets("DataExport")
  Set sh2 = Sheets("FilterCriteria")
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  a = sh1.Range("A2:A" & lr).Value2
  b = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp)).Value
  Set r = sh1.Range("A" & lr + 1)
  For i = 1 To UBound(a)
    exists = False
    For j = 1 To UBound(b)
      If a(i, 1) Like "*" & b(j, 1) & "*" Then
        exists = True
        Exit For
      End If
    Next
    If exists = False Then
      Set r = Union(r, sh1.Range("A" & i + 1))
      counter = counter + 1
    End If
  Next
  If counter > 0 Then
    vbAnswer = MsgBox(counter & " Rows will be deleted. Do you want to continue?", vbYesNo, "Delete Rows Macro")
    If vbAnswer = vbYes Then r.EntireRow.Delete
  Else
    MsgBox "There are no records to delete"
  End If
End Sub

 

[JLGWhiz]

Message: Morning, was wondering if you could help me out with the code you supplied. The other gentlemen was extremely helpful, but i just couldn't get his code to work with the project. Yours is working perfectly, with 1 exception, is there a way to limit the filter to everything before the first "-" in the naming structure? (e.g. our naming structure looks like ABCD-DC-001 or 123AB-DC-001) the reason i ask is its filtering everything, but a few of the items left don't belong there, but its because they are ending in ABC and not beginning with it.

After looking at the previous posts, I am unable to understand exactly what the problem is. Which sheet does the names with the dash marks appear? Could it be fixed by changing your filter criteria on the worksheet? Can you show a specific example of how a number would apppear on the filter sheet but not be identified on the DataExport sheet?