Results 1 to 13 of 13

Delete rows that DO NOT contain criteria texts

Threaded View

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    8

    Delete rows that DO NOT contain criteria texts

    Hi

    I am novice excel user and trying to search and found solution to my issue, I came close to it but not yet got what I wanted exactly. I am looking for a macro to delete rows that DO NOT contain criteria. But what I could achieve so far is deleting all rows except the first word ie ACC here. My code so far is as follows

    Sub DeleteRows()
    Dim lngCounter As Long
    Dim lngLastRow As Long
    Dim rngFound As Range
    Const cstrSEARCH As String = "SYMBOL"
    
    Sheets("Sheet1").Select
    Set rngFound = Rows("1:1").Find(what:=cstrSEARCH, _
        LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns)
    If Not rngFound Is Nothing Then
      lngLastRow = Cells(Rows.Count, rngFound.Column).End(xlUp).Row
      For lngCounter = lngLastRow To 2 Step -1
        Select Case (Cells(lngCounter, rngFound.Column).Value)
          Case Is <> "ACC", "AMBUJACEM", "ASIANPAINT", "AXISBANK", "BAJAJ-AUTO", "BHARTIARTL", "BHEL", "BPCL", "CAIRN", "CIPLA,COALINDIA", "DLF", "DRREDDY", "GAIL", "GRASIM", "HCLTECH", "HDFC", "HDFCBANK", "HEROMOTOCO", "HINDALCO", "HINDUNILVR", "ICICIBANK", "IDFC", "INFY", "ITC", "JPASSOCIAT", "JINDALSTEL", "KOTAKBANK", "LT", "LUPIN", "M&M", "MARUTI", "NTPC", "ONGC", "PNB", "POWERGRID", "RANBAXY", "RELIANCE", "RELINFRA", "SBIN", "SESAGOA", "SIEMENS", "SUNPHARMA", "TATAMOTORS", "TATAPOWER", "TATASTEEL", "TCS", "ULTRATECH", "WIPRO"
            Rows(lngCounter).Delete
          Case Else
            'keep record
        End Select
      Next lngCounter
    End If
    
    Set rngFound = Nothing
    
    End Sub
    I see some issue at code marked in red. Need help resolving this. Thanks, in advance
    Last edited by arlu1201; 12-15-2012 at 05:33 AM.

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1