[VBA] Only run macro in certain column

[stupideye]

Hello,

I have what is probably a simple question - I have this code I grabbed from elsewhere, I want it to only run in column A on all sheets (rather than on all columns as it does now):

Sub DeleteRows()  Dim WS As Worksheet
  On Error Resume Next
  For Each WS In Sheets
    With WS.UsedRange
      .Replace "*Remove*", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
  Next
End Sub

Any assistance on this one? Thank you in advance!

 

[Joe4]

I made the one modification to your code:

Sub DeleteRows()
  Dim WS As Worksheet
  On Error Resume Next
  For Each WS In Sheets
    With WS.Columns("A:A")
      .Replace "*Remove*", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
  Next
End Sub

and ran it against your data sample, and it did not touch the value in column D.
Instead it deleted the values from column A in the last two rows, but only deleted the cells, shifting the other columns in that row over one column to the left.

"Stain Remover" is still left in column D. So I don't see how that can be deleted from that code.

I tried Fluff's code, and it deleted the whole row instead of the cell. If you actually want to delete the whole row, you need to not use the Intersect statement, and use the delete row command like he has set it up. His also did not replace or delete the row with "Remove" in column D.

A few questions thoiugh:
- Are you copying the code exactly as we have written it?
- Are there are merged cells on your sheets?
- Are all these values hard-coded or are any of them the result of formulas (if so, which ones)?
- What version of Excel are you using?

 

[stupideye]

I made the one modification to your code:

Sub DeleteRows()
  Dim WS As Worksheet
  On Error Resume Next
  For Each WS In Sheets
    With WS.Columns("A:A")
      .Replace "*Remove*", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
  Next
End Sub

and ran it against your data sample, and it did not touch the value in column D.
Instead it deleted the values from column A in the last two rows, but only deleted the cells, shifting the other columns in that row over one column to the left.

"Stain Remover" is still left in column D. So I don't see how that can be deleted from that code.

I tried Fluff's code, and it deleted the whole row instead of the cell. If you actually want to delete the whole row, you need to not use the Intersect statement, and use the delete row command like he has set it up. His also did not replace or delete the row with "Remove" in column D.

A few questions thoiugh:
- Are you copying the code exactly as we have written it?
- Are there are merged cells on your sheets?
- Are all these values hard-coded or are any of them the result of formulas (if so, which ones)?
- What version of Excel are you using?

Both yours and Fluff's code changed values with "remove" in column D to #N/A, but didn't delete the rows. Unsure as to why. I copied exactly as written, no merged cells, no formulas, and Excel 2016.

But no big deal, this significantly speeds up my work even if it doesn't 100% do what I was hoping for. A big thank you to everyone who helped out with this one, appreciate it as always!

 

[Joe4]

It seems to work for us, which leads me to believe that there is *something* different about the data you are working on. It could be some small details that you might not think should affect things.

If you want to upload your data file to a file sharing site, we can download it and see exactly what you are working with, and test out the code on your exact data.
I cannot do that from my present location, but can do it when I am at home later tonight.