vba delete rows with cells not in a list

[katekoz]

Hello,
I've seen some variations of this on the forum, but not really what I'm looking for. I have a list of users that's created through a Pivot Table, which I named "UserPivot." I also have a set of data on a worksheet. I want to check through that list of data and, if the username in column D doesn't appear in UserPivot, I want to delete the row of data. Here is what I have so far. I know my "If Not UserPivot..." line is not right, but I'm just not sure how to reference that little pivot table!

Dim r As Integer
Dim UserPivot As Object

For r = ThisWorkbook.Sheets("New Data Add").UsedRange.Rows.Count To 1 Step -1

If Not UserPivot.Exists(.Cells(r, "D").Value) Then
ThisWorkbook.Sheets("New Data Add").Rows(r).EntireRow.Delete

End If
Next

 

[katekoz]

It doesn't seem to have worked. It deleted a lot of rows, but still left some lines of all the different users - the ones I wanted to keep and the ones I didn't. Not sure why. ??

 

[CSmith]

Try this...
As I said earlier deleting while traversing the collection is causing skipped rows, but going in reverse will fix this I believe.

  For Each Row In ThisWorkbook.Sheets("Inputs").PivotTables(1).DataBodyRange.Rows
    SearchList = SearchList & Row.Cells(Row.Count, SearchColumn) & "|"
  Next Row
  lr = ThisWorkbook.Sheets("New Data Add").UsedRange.Rows + ThisWorkbook.Sheets("New Data Add").UsedRange.Row
  sr = ThisWorkbook.Sheets("New Data Add").UsedRange.Row
  For Row = lr to sr step -1
    If 0 = InStr(1, SearchList, ThisWorkbook.Sheets("New Data Add").Cells(Row, SearchColumn)) Then
      Row.EntireRow.Delete
    End If
  Next Row

 

[katekoz]

Am I supposed to set a value for SearchList? If so, what is it supposed to be?

 

[katekoz]

Also, in this part of the code, where it used SearchColumn... this is putting the pivot table values into a string to reference later, correct? If so, should I change the "SearchColumn" to just 1? The pivot table has only one column. It's the other table, where the rows are being deleted, that needs to look at Column D (4). Unless I'm reading this wrong. ??

 

[katekoz]

Also, in this part of the code, where it used SearchColumn... this is putting the pivot table values into a string to reference later, correct? If so, should I change the "SearchColumn" to just 1? The pivot table has only one column. It's the other table, where the rows are being deleted, that needs to look at Column D (4). Unless I'm reading this wrong. ??

For Each Row In ThisWorkbook.Sheets("Inputs").PivotTables(1).DataBodyRange.Rows
SearchList = SearchList & Row.Cells(Row.Count, SearchColumn) & "|"
Next Row

 

[katekoz]

Ok, changing that "SearchColumn" to 1 worked, and now it creates a list of all the users from the pivot table with | in between as expected.

Now, however, it's erroring at the "For Row" line. It says, "Type Mismatch."
For Row = ThisWorkbook.Sheets("New Data Add").UsedRange.Row To ThisWorkbook.Sheets("New Data Add").UsedRange.Rows + ThisWorkbook.Sheets("New Data Add").UsedRange.Row

 

[katekoz]

Ok, I tried the lr and sr code, and I still get a Type Mismatch at the lr line. Perhaps I defined it wrong? I used Dim lr as Integer - should it be something else?

 

[CSmith]

My apologies it should have read:

  lr = ThisWorkbook.Sheets("New Data Add").UsedRange.Rows.Count + ThisWorkbook.Sheets("New Data Add").UsedRange.Row

Ok, I tried the lr and sr code, and I still get a Type Mismatch at the lr line. Perhaps I defined it wrong? I used Dim lr as Integer - should it be something else?

 

[katekoz]

Now the lr and the sr are both going through ok, but it's getting stuck with an "Object required" on the line:

Row.EntireRow.Delete

It's so close to running through!

 

[CSmith]

So sorry forgot that code was adapted from code that stepped forward through all rows using row range object.

Try:

    ThisWorkbook.Sheets("New Data Add")..Cells(row,1).EntireRow.Delete

Now the lr and the sr are both going through ok, but it's getting stuck with an "Object required" on the line:

Row.EntireRow.Delete

It's so close to running through!