I am having an issue with EXCEL. I am trying to loop through some data in Column A to match those values against a list on another sheet. If the names appear on sheet 2 it should select that row and delete it as well as delete all rows that follow that have a blank cell in column A until it reaches the next non-empty cell.
I have made some progress with your suggestions. Here is a newer 'real life' sample of the data I have.
and here is what it should look like after.
The VBA I am currently using is:
Option Explicit
Sub RemoveDuplicates()
Application.ScreenUpdating = False
Dim rCrit As Range 'Range To Search For Supervisor names
Dim rFilt As Range 'Range To Remove Supervisor names
Dim lLoop As Long
Dim statsSheet As Worksheet
Dim supervisors As Worksheet
Dim i As Long 'looking for blank cells under the supervisor name
Dim values As Range
Dim counter As Integer
Set values = Range("a:a")
Set rCrit = Worksheets("supervisors").Range("A1", Worksheets
("supervisors").Range("A" & Rows.Count).End(xlUp))
Set rFilt = Worksheets("statssheet").Range("A1", Worksheets
("statssheet").Range("A" & Rows.Count).End(xlUp))
For lLoop = rCrit.Rows.Count To 1 Step -1
If WorksheetFunction.CountIf(rCrit, rFilt(lLoop).Value) > 0 Then
Worksheets("statssheet").Rows(lLoop).Delete shift:=xlUp
End If
For i = 1 To values.Cells.Count - 1
If IsEmpty(values.Cells(i)) Then
Worksheets("statssheet").Rows(i).Delete shift:=xlUp
End If
Next i
Next lLoop
Application.ScreenUpdating = True
End Sub
unfortunately when I run this code it only eliminates the top supervisor name, and it deletes ALL data for every associate and looks like this.
Your type mismatch error happens at "Set values = Rows(rFilt)". The Rows property parameter should be an integer, not a range.
Example:
I think there are other problems with your code, but maybe fix this one first and we'll see where we go from there.