[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]

Try replacing:

With WS.UsedRange

with:

With WS.Columns("A:A")

 

[Fluff]

How about

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

 

[stupideye]

I appreciate the responses, but unfortunately neither of these seemed to work. Not sure why..

To clarify the issue I'm trying to solve: I am looking to delete rows for items that were removed from product listings, column A will have a variation of "remove" (ex: "removed", "removed and moved to...", "please remove").
The original code would work fine for this but some product descriptions (which are in column D) have the word "remove" in them ("stain remover").

There are 40-80 sheets per workbook I'm doing this on (just 2 workbooks), and most sheets have a few of these "remove" instances, so a find+replace is a immense amount of manual work.

Thanks for the help so far, anything further is really appreciated!

 

[Fluff]

In what way didn't they work?
Not deleting rows, deleting rows that shouldn't be deleted?

 

[stupideye]

In what way didn't they work?
Not deleting rows, deleting rows that shouldn't be deleted?

I should have clarified - it's deleting rows that shouldn't be deleted (like the "stain remover" example). The macro doesn't seem to be confined to column A.

 

[Fluff]

It shouldn't be. Do you have values such as #N/A, #VALUE!, #NAME? etc in col A before you run the code?

 

[Joe4]

It might be helpful if you could post a small sample of your data, and from that sample, let us know which rows should be deleted.
There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[stupideye]

It shouldn't be. Do you have values such as #N/A, #VALUE !, #NA ME? etc in col A before you run the code?

No error values in column A, just checked. Only blank cells, numerical values, or text is ever put in this column - never formulas.

Looking at column D after running the macro, I see that the "stain remover" example is now a #N/A value.. so it seems the macro is doing the .Replace part with columns other than A, but the deleting rows is (correctly) only affecting column A items.

 

[stupideye]

It might be helpful if you could post a small sample of your data, and from that sample, let us know which rows should be deleted.
There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Hopefully this helps:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]132[/TD]
[TD]Item1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1684[/TD]
[TD]649[/TD]
[TD]Item2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]52164[/TD]
[TD]589[/TD]
[TD]Stain Remover[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6465[/TD]
[TD]147[/TD]
[TD]Item4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]321[/TD]
[TD]654[/TD]
[TD]Item5[/TD]
[/TR]
[TR]
[TD]Remove[/TD]
[TD]125[/TD]
[TD]147[/TD]
[TD]Item6[/TD]
[/TR]
[TR]
[TD]Pls remove[/TD]
[TD]1454[/TD]
[TD]134[/TD]
[TD]Item7[/TD]
[/TR]
</tbody>[/TABLE]

The bold rows should be deleted, but the row with "Stain Remover" in column D should not be deleted.