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

 

[CSmith]

Something like this?
It should be noted that you might store the row numbers in an array and delete from last to first as deleting rows while looping through might have unintended consequences.

  For each Row in PivotTable.DataBodyRange.Rows
    SearchList = SearchList & Row.Cells(Row.Count, SearchColumn) & "|"
  Next Row

  For Each Row In ThisWorkbook.Sheets("New Data Add").UsedRange.Rows
    If 0 = InStr(1, SearchList, Row.Cells(Row.Count, SearchColumn)) Then
      Row.EntireRow.Delete
    End If
  Next Row

 

[katekoz]

That looks good! I'm on my way out of the office, but will try it on Monday and get back to you with the results.

 

[katekoz]

Hi CSmith,

One thing I noticed is that I need to search a pivot table that's in a different sheet than the one with the rows I'm deleting. It is the only pivot on that sheet. I tried to add that specification to the code, but I get an error of "Object doesn't support this property or method." How could I direct the search to the "Inputs" sheet for the pivot table?

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

For Each Row In ThisWorkbook.Sheets("New Data Add").UsedRange.Rows
If 0 = InStr(1, SearchList, Row.Cells(Row.Count, SearchColumn)) Then
Row.EntireRow.Delete
End If
Next Row

Also, I've never used InStr before, so I'm wondering - how does it know which column of each row to search for the match in the string? For instance, my data has 14 columns and the value we're checking for is in column D. How does it know to check D, or does it simply check all the values in the entire row at once?

Thanks for your help!

 

[CSmith]

Well, I thought it obvious that you would need to set the "SearchColumn" variable (Integer/Long) to the appropriate column for searching, however you could also use this to loop through ALL the columns as well. For Column D you would need to have the lines

Dim SearchColumn as Integer
SearchColumn = 4

above the code I gave you earlier.

Is the pivot table named "Inputs" or? Is there any other pivot table in the workbook? Also, what exactly are you attempting to do with this code? I feel you might be trying to clear the data added from the items already showing in the pivot table? You might supply a sample of the data so I can be sure what you want is what you will get.

 

[katekoz]

Now that you say it, I do see that the SearchColumn could be set that way. I am very new to macros, so I'm trying to feel my way through them while using them without training. Trial by fire.

The sheet with the pivot table is named "Inputs" and the pivot table that's being searched is the only pivot table on that sheet, so I didn't name it, though I could if necessary.

Separately, on a sheet called "New Data Add" is a long list of data made up of many users. I only want to report on a small set of them (the set that's listed in the pivot table in "Inputs"). I'd like to delete any row of data in "New Data Add" that is a username other than the ones in the pivot table in "Inputs."

I'm trying to make the sheet do all data updates through a macro without users ever deleting or rearranging the data, and the only update I have left is to delete these extra users from the data set.

 

[CSmith]

So you already have code to insert the new user data to the sheet that the pivot table is being built from, and now you want to take any user found in the pivot table and remove that data from the new data add?

 

[CSmith]

I think this is the method you are looking for to access the data you are after to build your search string. Just remember to set SearchColumn to the correct column in the pivot table containg the user(s)names.

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

/QUOTE]

 

[katekoz]

The pivot table is built from an entirely separate set of data on a different sheet. What I'm doing now is just one part of the very large workbook with many pivots, tables, charts, etc. It has over two dozen sheets.

"now you want to take any user found in the pivot table and remove that data from the new data add" - I want to remove any user not in the pivot table from the new data add.

 

[CSmith]

The code change I gave above I believe will do what you want

The pivot table is built from an entirely separate set of data on a different sheet. What I'm doing now is just one part of the very large workbook with many pivots, tables, charts, etc. It has over two dozen sheets.

"now you want to take any user found in the pivot table and remove that data from the new data add" - I want to remove any user not in the pivot table from the new data add.