Announcement

Collapse
No announcement yet.

Delete Rows Meeting Criteria or Condition

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete Rows Meeting Criteria or Condition



    Hello forum members. I've searched the forum for the past few hours and found answers that seemed they might work, but didn't. I have a spreadsheet with 3023 rows and columns A-L. Here's a small sampling of Column A (with heading "sku" included, copied exactly as it appears in the spreadsheet):

    A
    sku
    3102-0400-100000
    3102-0400-200000
    3102-0500-100000
    3102-0500-200000
    3102-0600-100000
    3102-0600-200000
    3102-0700-100000
    3102-0700-200000

    I need to delete every row in which column A includes the text "200000".

    Although the above sampling shows "200000" appears in every other row, that is not the case in all 3023 rows.

    Most gratefully,
    Diana

  • #2
    Re: Deleting Rows Which Contain Specific Text

    Hi

    Try selecting your headings - go to Data- Autofilter - select "200000" from the drop down in column A - then delete the rows

    Comment


    • #3
      Re: Deleting Rows Which Contain Specific Text

      Hi

      How about

      Code:
      Sub bbb()
        For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
          If InStr(1, Cells(i, 1).Value, "200000") Then Cells(i, 5).EntireRow.Delete
        Next i
      End Sub

      Tony

      Comment


      • #4
        Re: Deleting Rows Which Contain Specific Text

        Hi diana777,

        Welcome!

        This will do it.

        To use, copy the code below, press <ALT+F11> to open the VBE.

        INSERT\MODULE (if needed) and paste the code into the module.

        To run press <AlT+F8>, select delRow and click Run (or to set a hot key click 'options' and assign a <CTRL + key> or <CTRL + SHIFT+key> combo.


        Code:
        Sub delRows()
        
        Dim lastrow As Long
        
            Application.ScreenUpdating = False
            
            'Get lastrow of data Col A
            lastrow = Range("A65536").End(xlUp).Row
            
            'Loop on all data rows (last to first)
            For i = lastrow To 2 Step -1
                'Find string "200000"
                If (Right(Cells(i, 1), 6) = "200000") Then
                    'Found: delete row
                    Cells(i, 1).EntireRow.Delete
                End If
                'Not found, check next row
            Next i
            
            Application.ScreenUpdating = True
        
        End Sub
        cheers,

        dr
        Cheers,

        dr

        "Questions, help and advice for free, small projects by donation. large projects by quote"

        http://www.ExcelVBA.joellerabu.com

        Comment


        • #5
          Re: Deleting Rows Which Contain Specific Text

          Without looping

          Sub DelRows()
          With Range("A1", [a65536].End(xlUp))
          .AutoFilter Field:=1, Criteria1:="=*200000*"
          .Offset(1, 0).Resize(.Rows.Count - 1, 1).EntireRow.Delete
          .AutoFilter
          End With
          End Sub


          HTH
          Kris

          ExcelFox

          Comment


          • #6
            Re: Deleting Rows Which Contain Specific Text

            Hello again forum members. Thank you all for your quick responses. I tried each suggestion here as part of a learning experience. I'm a web designer who normally doesn't get too much involved in working with excel in developing spreadsheets. Spreadsheets are usually sent to me in final form. This forum has stimulated my desire to learn more about excel. I'm up to my eyebrows in Flash courses right now, but when I get through more of those courses, I'm coming back here for some first class Excel training.

            Here are the results of applying the recommendations on this page, from top to bottom:

            1.
            Try selecting your headings - go to Data- Autofilter - select "200000" from the drop down in column A - then delete the rows.
            Result: The "200000" was not in the drop down box in column a as an isolated choice, but rather combined with the other elements of each cell containing "200000"

            2. acw: It might have worked, but it seems it was deleting each row with "200000" one at a time, refreshing the screen after each action. I had to CTRL - ALT - DELETE to stop it for fear I might have had to wait for it to delete several hundred rows one at a time.

            3. rbrhodes: Worked perfectly (but see note at the end of this post)

            4. Krishnakumar: Also worked perfectly (but see the following note)

            NOTE: Started with 3023 rows. After running rbrhodes' solution, ended up with 2445 rows. After running Krishnakumar's solution, ended up with 2432 rows.

            To follow through, I did a "Find" on "200000" in column A, and discovered in rbrhodes' solution 13 cells in column A that contained the "200000" in them. They were different from the other cells shown in my example at the beginning of this thread, as they were followed by more text, as follows:

            3250-0400-200000-FL

            Each one had the "-FL" after the "200000". Performing the "Find" after running Krishnakumar's solution returned negative results, with no "200000" found in column A.

            I'll submit the revised spreadsheet to my design client tomorrow morning with a gentle tip: "compare to original before using". : - )

            Thank you all tons and tons,

            Diana

            Comment


            • #7
              Re: Deleting Rows Which Contain Specific Text

              Performing the "Find" after running Krishnakumar's solution returned negative results, with no "200000" found in column A.
              What do you mean by negative results?
              Kris

              ExcelFox

              Comment


              • #8
                Re: Deleting Rows Which Contain Specific Text

                See Delete Rows Meeting Criteria

                Comment


                • #9
                  Re: Delete Rows Meeting Criteria or Condition

                  What do you mean by negative results?
                  What I meant was the search resulted in no instances of "200000", which is a "negative" result from the search utility, but a "positive" result for your code.

                  Thank you for the follow up.

                  Diana

                  Comment


                  • #10
                    Re: Delete Rows Meeting Criteria or Condition

                    OK. fine.
                    Kris

                    ExcelFox

                    Comment


                    • #11
                      Re: Delete Rows Meeting Criteria or Condition

                      Dave Hawley, Super Administrator: See Delete Rows Meeting Criteria
                      Thank you Dave. I had read that detailed article before I made this post, but, dumb-dumb that I am, I don't know enough about the code to figure out from the article how to create it for the problem which prompted opening this thread. This state of dumb-dumbness will change as I get into it. I hope.

                      Sincerely,

                      Diana

                      Comment


                      • #12


                        Re: Delete Rows Meeting Criteria or Condition

                        Don't be too hard on yourself, we all started the same.

                        Comment

                        Working...
                        X