Check cells for certain value, delete row if present

[xspike77x]

So I have numerous sheets in a workbook and I would like my macro to go through all the sheets in the workbook and delete those rows in each sheet that contains a cell with "PY" in it. Here is what I have and it is not working. What am I doing wrong?

Dim WS As Worksheet
Dim r As Long
Dim c As Long

r = 1
c = 1

For Each WS In ThisWorkbook.Worksheets

For c = 1 To 20
For r = 1 To 200

If Cells(r, c).Value = "PY" Then
Rows(r).Delete
r = r - 1
End If

Next r
Next c

Next WS

 

[nbrcrunch]

Why are you decrementing r? "r = r - 1"
Since you are cycling through the rows starting at row 1, subtracting one would definitely cause a bomb.

 

[lancerj017]

Try:

Sub DeletePY()
Dim Lr As Long
Dim Lc As Long
Dim WS As Worksheet
Dim c As Range
Lr = Cells(Rows.Count, "A").End(xlUp).Row
Lc = Cells(1, Columns.Count).End(xltoleft).Column
For Each WS In ThisWorkbook.Worksheets
    For Each c In Range(Cells(1, 1), Cells(Lr, Lc))
        If c.Value = "PY" Then c.Delete
    Next
Next
End Sub

 

[Kolkinari]

I think you want something like this.

Sub DeleteRow()
Dim WS As Worksheet
Dim r As Long
Dim c As Long


For Each WS In ThisWorkbook.Worksheets

For c = 1 To 20
For r = 200 To 1 Step -1

If Cells(r, c).Value = "PY" Then
Rows(r).Delete
End If

Next r
Next c

Next WS
End Sub

 

[Rick Rothstein]

Assuming the PY in your cells are constants (that is, they are not the result of formulas), then you can use this macro...

Sub RemoveRowsWithPY()
  Dim WS As Worksheet
  For Each WS In Worksheets
    WS.Cells.Replace "PY", "#N/A", xlWhole
    On Error Resume Next
    Intersect(Columns("A"), WS.Cells.SpecialCells(xlConstants, xlErrors).EntireRow).EntireRow.Delete
    On Error GoTo 0
  Next
End Sub

 

[xspike77x]

Mr Rothstein,

So far yours has been the only solution to do anything different than what I had. In the cases where there is "PY" in a given cell, your suggested code does change "PY" to "#N/A," however, none of these rows end up being deleted.

Regarding and earlier comment, "PY" in the cell is not the result of a formula, it is a keyed value. Still searching for a solution. Thank you everyone for the input so far. Any other ideas? I will also note that my data on each sheet is not one solid block of data, taht is why I specify the range of rows and columns to analyze. The rows and columns will not exceed the amounts provided, namely, 200 and 20 respectively.

 

[Rick Rothstein]

I will also note that my data on each sheet is not one solid block of data, that is why I specify the range of rows and columns to analyze. The rows and columns will not exceed the amounts provided, namely, 200 and 20 respectively.

Did you change the code I posted in any way (to implement what you have said above)? Can you post a copy of the workbook (before the #N/A values replaced the PY values) to one of the free file-sharing websites so we can test out our ideas directly on your data. I ask because the code I posted worked perfectly on the dummy data I set up, so there is apparently something different about you worksheets that I was not able to account for in my tests. One such file-sharing website that seems to work well is this one....

http://www.box.net/files

 

[xspike77x]

I actually was able to make it work before I saw your newest post. Only a slight modification of adding WS. before Cells and Rows(r).Delete made it work perfectly. This is what I ended up with.

Dim WS As Worksheet
Dim r As Long
Dim c As Long

r = 1
c = 1

For Each WS In ThisWorkbook.Worksheets

For c = 1 To 20
For r = 1 To 200

If WS.Cells(r, c).Value = "PY" Then
WS.Rows(r).Delete
r = r - 1
End If

Next r
Next c

Next WS