vba Delete Row(s) not matching table criteria Macro

NGB82KS

Board Regular
Joined
Nov 7, 2019
Messages
82
Office Version
  1. 2016
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.
 
Well now it give me the number it will delete, but says delete message of range class failed and highlights r.EntireRow.Delete
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.

1581042817118.png
 
Upvote 0
What changed from your first test data to this test data? Do you have merged cells, the sheet is protected, something different?
 
Upvote 0
you can remove the table and pass the data to a normal range and try again
 
Upvote 0
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.
 
Upvote 0
that's very weird, try the following

VBA Code:
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
 
Upvote 0
Or try this other alternative.

VBA Code:
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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