Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: [VBA] Only run macro in certain column

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow [VBA] Only run macro in certain column

    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!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,041
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    Try replacing:
    Code:
    With WS.UsedRange
    with:
    Code:
    With WS.Columns("A:A")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,167
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    How about
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    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!

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,167
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    In what way didn't they work?
    Not deleting rows, deleting rows that shouldn't be deleted?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    Quote Originally Posted by Fluff View Post
    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.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,167
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    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 by Fluff; Oct 9th, 2019 at 03:37 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,041
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    Quote Originally Posted by Fluff View Post
    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.

  10. #10
    New Member
    Join Date
    Aug 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Only run macro in certain column

    Quote Originally Posted by Joe4 View Post
    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 A Col B Col C Col D
    1 123 132 Item1
    4 1684 649 Item2
    0 52164 589 Stain Remover
    6465 147 Item4
    321 654 Item5
    Remove 125 147 Item6
    Pls remove 1454 134 Item7

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •