Results 1 to 2 of 2

Keyword Identification and Changes

Threaded View

  1. #1
    Registered User
    Join Date
    11-06-2017
    Location
    iran
    MS-Off Ver
    2013
    Posts
    4

    Keyword Identification and Changes

    hi
    4c7b-Untitled-picture.png
    I'm doing my job using the code below
    But In fact, the words (keywords) I want to delete are in the column "c"

    I mean the keyword is not just a word
    I have a lot of words in the column that is added to them daily.
    Is there a friend who corrects my code according to the image.

    thanks

    Sub Demo()
    Dim dataRng As Range
    Dim foundCell As Range
    Dim ary() As String
    On Error Resume Next
    ' remove empty cells
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    ' group cells by "t.me/canxl/" and add name for each group
    Set dataRng = ActiveSheet.UsedRange 'Range("A1:A19")
    With dataRng
        Set c = .Find("t.me/canxl/", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                Set c = .FindNext(c)
                ReDim Preserve ary(i)
                ary(i) = c.Row
                i = i + 1
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
        First = LBound(ary)
        Last = UBound(ary)
        For i = First To Last - 1
            For j = i + 1 To Last
                If ary(i) - ary(j) > 0 Then
                    Temp = ary(j)
                    ary(j) = ary(i)
                    ary(i) = Temp
                End If
            Next j
        Next i
    Dim xName As Name
    For Each xName In Application.ActiveWorkbook.Names
        xName.Delete
    Next
    For i = 0 To UBound(ary) - 1
    ActiveWorkbook.Names.Add Name:="Group" & i + 1, RefersTo:=Range(Range("A" & ary(i)), Range("A" & ary(i + 1) - 1))
    Next
    ActiveWorkbook.Names.Add Name:="Group" & UBound(ary) + 1, RefersTo:=Range(Range("A" & ary(UBound(ary))), dataRng.End(xlDown))
    ' search which group contains keyword and delete the named range
    With dataRng
        Set c = .Find("Keyword", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                Set c = .FindNext(c)
                For Each xName In Application.ActiveWorkbook.Names
                If Not Intersect(c, Range(xName)) Is Nothing Then
                If Rng Is Nothing Then
                Set Rng = Range(xName)
                Else
                Set Rng = bigRange
                End If
                Set bigRange = Application.Union(Range(xName), Rng)
                xName.Delete
                End If
                Next
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
    bigRange.Delete
    If Error.Count > 0 Then
    Error.Clear
    End If
    End Sub
    Last edited by Leith Ross; 11-06-2017 at 08:34 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 11
    Last Post: 11-02-2017, 11:02 AM
  2. Search cells C1:C3000 for keyword and if exists place a keyword in A1:A3000
    By GregQuick in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-24-2015, 07:38 PM
  3. Replies: 6
    Last Post: 03-18-2014, 11:16 AM
  4. Filter List by Keyword & copy the result in column by keyword as header
    By kitunga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-21-2013, 07:16 AM
  5. [SOLVED] Need to chck if a keyword is present in a text string and return keyword if yes
    By Jekaterina in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-16-2012, 05:55 PM
  6. Replies: 2
    Last Post: 07-13-2012, 04:02 AM
  7. Replies: 1
    Last Post: 12-27-2010, 08:53 AM

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