[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!
 
I made the one modification to your code:
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?
 
Last edited:
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I made the one modification to your code:
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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