Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



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

Thread: Delete Rows Meeting Criteria or Condition

  1. #1
    Join Date
    21st November 2006
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd November 2005
    Posts
    894

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    6th May 2005
    Posts
    1,036

    Re: Deleting Rows Which Contain Specific Text

    Hi

    How about

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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.


    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Deleting Rows Which Contain Specific Text

    Without looping

    VB:
    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

  6. #6
    Join Date
    21st November 2006
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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?

  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

  9. #9
    Join Date
    21st November 2006
    Posts
    4

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Delete Rows Meeting Criteria or Condition

    OK. fine.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Delete Blank Rows & Rows Below Meeting Condition
    By saturner1946 in forum Excel General
    Replies: 9
    Last Post: June 26th, 2008, 12:05
  2. Delete Rows Meeting Condition For Pivot Table Source
    By Moster in forum Excel General
    Replies: 2
    Last Post: April 29th, 2008, 12:51
  3. Delete Rows Not Meeting Multiple Criteria
    By TMWAGNER77 in forum Excel General
    Replies: 8
    Last Post: March 1st, 2008, 08:26
  4. Delete Rows Not Meeting Criteria
    By viktora in forum Excel General
    Replies: 1
    Last Post: February 28th, 2007, 05:06
  5. Delete Rows Meeting Condition
    By Daniels in forum Excel General
    Replies: 3
    Last Post: August 26th, 2003, 18:07

Bookmarks

Posting Permissions

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