Remove rows from another list using excel VBA - Help!!!

[sage123]

Hi all,

I am trying to use VBA to automatically delete rows of data in my 'ZTDA Tracker' column B from a list of data in the 'Mids' tab in column D. There are 1116 rows of data in column D of the 'Mids' tab.

I am looking for some VBA code to search through column B of 'ZTDA Tracker' and delete the entire row if a value matches that found in column D of the Mids tab.

I have found and tried to manipulate 2 sets of code below:


1) This one deletes all information from Row 3 across the ZTDA tab downwards as well as taking out the value in cell B2.

Sub CompassAccFilter()

Dim lrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("ZTDA Tracker")
Set ws2 = ThisWorkbook.Sheets("Mids")
With ws1
lrow = .Range("B" & .Rows.Count).End(xlUp).Row
With .Range("B2:B" & lrow)
.Formula = "=IFERROR(MATCH(D1," & ws2.Name & "!D:D,0),"""")"
.Value = .Value
.AutoFilter 1, "<>"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

2) This code only deletes 3 rows of data out of the ZTDA tracker tab - all the same value. That value is, however, found in the 'Mids' tab so it is a step in the right direction!

Public Sub delete_selected_rows()
Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
Dim lastRow As Long

With Worksheets("ZTDA Tracker")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng1 = .Range("B2:B" & lastRow)
End With
Set rng2 = Worksheets("Mids").Range("D1:D1116")

For Each c In rng1
If Not IsError(Application.Match(c.Value, rng2, 0)) Then
'if value from rng1 is found in rng2 then remember this cell for deleting
If rngToDel Is Nothing Then
Set rngToDel = c
Else
Set rngToDel = Union(rngToDel, c)
End If
End If
Next c
If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
End Sub)

Any help you could give me would be greatly appreciated!

 

[James006]

Hello,

You could test following :

Public Sub delete_Selected_Rows()
' Search through column B of 'ZTDA Tracker'
' and delete the entire row if a value matches that found in column D of the Mids tab


Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
Dim lastRow As Long


  With Worksheets("ZTDA Tracker")
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    Set rng1 = .Range("B2:B" & lastRow)
  End With
  Set rng2 = Worksheets("Mids").Range("D1:D1116")


  For Each c In rng1
  ' if value from rng1 is found in rng2 then
  ' build range to Delete ...
    If Not IsError(Application.Match(c.Value, rng2, 0)) Then
      If rngToDel Is Nothing Then
        Set rngToDel = c
      Else
        Set rngToDel = Union(rngToDel, c)
      End If
    End If
  Next c
  
If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete


End Sub

Hope this will help

 

[sage123]

Hi James006,

Thank you for your quick reply! I have given your code a try but it does not seem to delete any rows from my ZTDA tab. I run a macro to organise data (formatting and filter) and another to delete any hidden rows after a filter has been applied before running this code - would this have an impact on how the VBA functions?

 

[James006]

Hi again,

Not sure to fully understand your constraints ...

Are you dealing with filtered rows ?

Are you dealing with hidden rows ?

 

[sage123]

Hi,

Once filtering and hidden rows have been removed, it'll just be ordinary data in a tab. There will be a filter option on my headers however as hidden rows will have already been removed there'll be no option to select any other data other than the information shown prior to running this vba code

 

[sage123]

Hi guys,

Really struggling to get the below code to work for me - could any of you lend any assistance? James006 helpfully tweaked my initial coding to get me a step closer but it isn't quite there yet!

I need VBA to automatically delete rows of data in my 'ZTDA Tracker' column B from a list of data in the 'Mids' tab in column D. There are 1116 rows of data in column D of the 'Mids' tab.

I am looking for some VBA code to search through column B of 'ZTDA Tracker' and delete the entire row if a value matches that found in column D of the Mids tab.




Public Sub delete_Selected_Rows()
' Search through column B of 'ZTDA Tracker'
' and delete the entire row if a value matches that found in column D of the Mids tab


Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
Dim lastRow As Long


With Worksheets("ZTDA Tracker")
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng1 = .Range("B2:B" & lastRow)
End With
Set rng2 = Worksheets("Mids").Range("D1:D1116")


For Each c In rng1
' if value from rng1 is found in rng2 then
' build range to Delete ...
If Not IsError(Application.Match(c.Value, rng2, 0)) Then
If rngToDel Is Nothing Then
Set rngToDel = c
Else
Set rngToDel = Union(rngToDel, c)
End If
End If
Next c

If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete


End Sub

Really appreciate the help you are all giving me!