Results 1 to 2 of 2

Thread: Delete Entire row if cell value does not have either two of specific word

  1. #1
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    12

    Delete Entire row if cell value does not have either two of specific word

    Data range starts from A9:M9, and last row varies on day to day basis.

    Adjacent cells like A1:M8, have data, which should not get disturbed.

    Column B has identifier flag, which in case is not there, entire row should be removed.

    So, Macro should
    1) search from A9:M9 till last available row (Range), and
    2) see, if row cell in Column B has either of the word 'XX' or 'YY', then it should leave entire row as it is and (row be retained)
    3) Else, If row Cell in Column B has word, other than 'XX' OR 'YY' macro should delete entire row is moved (shift) up, (ensure Range A1:M8) remains untouched.

    How to do this?

    Thanks

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Try

    Code:
    Option Explicit
    
    Sub kTest()
    
        Dim r As Range
        
        With Range("A9:m" & Range("a" & Rows.Count).End(xlUp).Row)
            .AutoFilter Field:=2, Criteria1:="<>T", Operator:=xlAnd, Criteria2:="<>v"
            On Error Resume Next
            Set r = .Cells(1).Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(12)'remove highlighted, if row 9 is not header
            If Not r Is Nothing Then
                r.EntireRow.Delete
            End If
            .AutoFilter
        End With
        
    End Sub
    Last edited by Admin; 02-25-2014 at 10:57 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 2
    Last Post: 09-12-2013, 02:13 PM
  2. Replies: 8
    Last Post: 07-01-2013, 03:52 PM
  3. Delete Entire Row For All Empty Cells In Column
    By johnreid7477 in forum Excel Help
    Replies: 4
    Last Post: 06-15-2013, 05:50 AM
  4. Delte a specific column and does not delete the top row
    By jffryjsphbyn in forum Excel Help
    Replies: 1
    Last Post: 06-13-2013, 02:00 PM
  5. Replies: 3
    Last Post: 12-05-2012, 09:51 PM

Posting Permissions

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