[VBA] Only run macro in certain column

stupideye

New Member
Joined
Aug 6, 2019
Messages
14
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):

Code:
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try replacing:
Code:
With WS.UsedRange
with:
Code:
With WS.Columns("A:A")
 
Upvote 0
How about
Rich (BB code):
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
 
Upvote 0
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!
 
Upvote 0
In what way didn't they work?
Not deleting rows, deleting rows that shouldn't be deleted?
 
Upvote 0
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.
 
Upvote 0
It shouldn't be. Do you have values such as #N/A, #VALUE!, #NAME? etc in col A before you run the code?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

Col ACol BCol CCol D
1123132Item1
41684649Item2
052164589Stain Remover
6465147Item4
321654Item5
Remove125147Item6
Pls remove1454134Item7

<tbody>
</tbody>

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

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top