Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Vba For Deleting If Not Containing A Given Keyword

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am looking for a VBA code to remove rows in which, column B does NOT contain "keyword1". The content of cells in column B is simple text such as "This is the text containing keyword1 in this cell".


Similar Excel Video Tutorials

Helpful Excel Macros

Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Delete Only the Text from Cells
- This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cell

Similar Topics







I have some data in columns with a campaign name, next row group name, further down the page some keywords that I need changing into rows on a new sheet.

Transpose doesn't work because it puts it all in one row and I need the first 2 rows to be repeated for every keyword, there can be upto 50 columns with this format that all need changing over. i.e

Campaign
Group


keyword1
keyword2
keyword3

becomes

Campaign Group Keyword1
Campaign Group Keyword2
Campaign Group Keyword 3

Thanks!


Please have a look at the attached sample sheet. The sheet named "category" has keywords under columns "Keyword1", "Keyword2", "Keyword3" & "Keyword4".

The sheet "Main" contains a column "Description" which will be searched against using these keywords..

The macro/function that I am looking for should search for all keywords in
"Keyword1", "Keyword2", "Keyword3" & "Keyword4".and if all of them exist in 1 line, it should stamp "Sub Category 1", "Sub Category 2", "Sub Category 3" & "Sub Category 3" besides "Description" column under the "Main" sheet.

I found a macro that works for a single keyword by looking only under "keyword1" but I need to get it working for multiple keywords.

For e.g "he" and "boy" are 2 keywords. If both are present on the same line under any of the "Description" rows, it should stamp the category "Gender" "Male"..

The current macro will stamp "Gender" "Male" only if "keyword"1 is "he is a boy" which means it is looking at the entire cell or phrase.

I have attached the macro that was found on the internet and a sample file with a preview of what I am looking for.

Thanking you in advance.


This forum has been incredibly useful as it's helped me *almost* get a project finished. The user Trebor76 is awesome and provided the code to use on another forum thread. I'm still having just one small hang up.

The code is looking for criteria in column A on the "master" sheet and if it meets that criteria it copies it and puts it on the appropriate sheet (and then deletes it from the master sheet).

Here's the code

VB:

Sub MoveAndDelete() 
     
    Dim lngRowPasteTo As Long 
     
    lngRowPasteTo = Sheets("KEYWORD1").Range("A65536").End(xlUp).Row + 1 
     
    Sheets("master").Select 
     
    Find_Range("KEYWORD1", Columns("A")).EntireRow.Copy _ 
    Destination:=Sheets("KEYWORD1").Range("A" & lngRowPasteTo) 
    Find_Range("KEYWORD1", Columns("A")).EntireRow.Delete 
     
    lngRowPasteTo = Sheets("KEYWORD2").Range("A65536").End(xlUp).Row + 1 
     
    Find_Range("KEYWORD2", Columns("A")).EntireRow.Copy _ 
    Destination:=Sheets("KEYWORD2").Range("A" & lngRowPasteTo) 
     
    Find_Range("KEYWORD2", Columns("A")).EntireRow.Delete 
     
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




The problem is that if it doesn't find a result (i.e. there is no "KEYWORD1" in column A) it pukes and I get the Run Time Error 91. I think I have to put something in there that would loop it or just move on in case no results but I'm too much of a novice to know for sure.

Anyway, hopefully someone can help me out with this.

Thanks!

I want to make a hyperlink from Code:

Dim m as long
Dim Keyword1 as String
Keyword1 = "Example"
Sheets("KeywordAnalysis").Cells(m, 3) = Keyword1

Sheets("KeywordAnalysis").Cells(m, 3)   'link from this cell


to Code:

Dim j as long
Sheets("Publications").Cells(j, 1)   'link to this cell


.

How does the code for this hyperlink look like?


I have a worksheet that is full of text cells in Column A and numbers in Column B. Column B represents the number of occurrences of the adjacent text in Column A for my data set. For instance below, KEYWORD A occurs 5 times and KEYWORD B occurs 3 times in the data. How can I get Excel to essentially expand this number of occurrences? So for KEYWORD A it would look at the number 5 in column B and give me 5 cells with KEYWORD A in them?

KEYWORD A 5
KEYWORD B 3


KEYWORD A
KEYWORD A
KEYWORD A
KEYWORD A
KEYWORD A
KEYWORD B
KEYWORD B
KEYWORD B


How to delete any row which contain "keyword1" in any cells of it?


So i'm try to automate a process that I currently do using filters.

Here is the problem:

I have a list of text in a column (usually 700 or so entries).

I need to remove any entries that contain 1 of 42 keywords.

So I need a fast way to search each cell for each one of the 42 key words and flag the cell for deletion if contains anyone of the 42.

example
A
1 This field has keyword1
2 This field has keyword2
3 This field doesn't have any of the 42 keywords


somewhere else I would have a list of keywords in a column.

Thanks for the help.


Hi,

I have a 3 values given in column A1, B1and C1

A1 - Client Name
B 1 Month
C1- Keyword1

I have a big list of clients starting from A3, list of months from B3 and list of different keywords from c3. Now I need a function where in it should match the value in A1,B1 and C1 from the list and give me the count of keyword 1 in cell D3. I guess there is a nested function of Product.

Many Thanks,
Cheers


I have a Google Adwords campaign with around 200 ad groups. I would like to add some keywords which follow a similar structure to each ad group. They will be like this:

*standard keyword text 1* + location 1
*standard keyword text 2* + location 1
*standard keyword text 3* + location 1
*standard keyword text 1* + location 2
*standard keyword text 2* + location 2
*standard keyword text 3* + location 2
*standard keyword text 1* + location 3
*standard keyword text 2* + location 3
*standard keyword text 3* + location 3
etc

If I would like to add more keywords after I have initially done this, in the future I will probably want to add more keywords to each location so it may go like this:

*standard keyword text 1* + location 1
*standard keyword text 2* + location 1
*standard keyword text 3* + location 1
*standard keyword text 4* + location 1
*standard keyword text 1* + location 2
*standard keyword text 2* + location 2
*standard keyword text 3* + location 2
*standard keyword text 4* + location 2
*standard keyword text 1* + location 3
*standard keyword text 2* + location 3
*standard keyword text 3* + location 3
*standard keyword text 4* + location 3

Does anyone know how I can construct a spreadsheet to help me do this quickly and easily? Is it even possible?


Hi, I'm trying to find an Excel formula or array, etc. to be able to do the following:

I have a column in a spreadsheet with about 50 text keywords/phrases (one keyword/phrase on each row) and I need to find which rows of article text contain one or more of these keywords. Then I have rows in the spreadsheet that contain the text content for articles with the following column headings:

Column A is the keyword list
Column B is the Article Title
Column C is the Author's Name
Column D is the Article Text
Column E is the Article Publication Date.

Each row in the spreadsheet contains the content for one article so I need the formula to check to see how many (if any) of the column A list of keywords exist in the article text cell of each row (column D). Also, if possible I would like the formula to tell me how many times each keyword exists in each article text.

So for example, if I have 1,000 rows articles about real estate in the spreadsheet and I have a list of keywords like:

mortgage loan, kitchen, realtor, house appraiser, plumbing, electrical

If an article contains the word "mortgage loan" twice, "house appraiser" once, and "electrical" three times, then I would like the formula result to indicate which keywords it found in that article, and if possible how many times it found each keyword in that article. (To calcluate each row separately I could use the fill-down command to copy the formula in a column to calculate this for each row of the 1,000 articles).

If possible, I would like the formula to list the result from highest keyword frequency to the lowest. So in this example, the result would be listed as:

electrical 3, mortgage loan 2, house appraiser 1 (if the keyword frequency amount can be listed in the result)

or else list the result as:
electrical, mortgage loan, house appraiser

Thank you very much for any advice you have!

Hi All,

I need to manipulate existing data in a spreadsheet, so that it can be used in a data feed somewhere else. There are 2 columns, one for SKU and one Keyword. The SKU is a 6 digit number, and each SKU can have multiple words associated with it (between 0-10).

SKU Keyword
1 book
1 read
1 author
2 movie
2 director
3 song
3 composer
3 singer
3 instrument

What I need for the data feed is to present the data like so:

SKU Keyword1 Keyword2 Keyword3 Keyword4 Keyword N
1 book read author
2 movie director
3 song composer singer instrument

I think this is achievable, but not sure how to do it. I am reasonably comfortable with excel but not VBA and haven't yet done pivot tables.
Perhaps now is the time? Any advice welcome.

Thanks in advance...

tony


Is there a way to search for a keyword in a spreadsheet and delete the cell if finding that keyword?

I mean deleting a cell if it contains a certain keyword (regardless of the other text in the cell)!


I have an excel sheet with columns of data that then needs to be transposed into rows, however each column is a different length and there are loads of columns so to do each one individually takes too long. I have managed to get the keywords over and walking through the macro it brings the top 2 titles over but the pulls down the wrong data.

Original columns look like this, with various numbers of keywords

campaign1
adgroup1
keyword1
keyword2
keyword3
keyword4
keyword5

this needs to move to the spreadsheet to look like this

campaign1 adgroup1 keyword1
campaign1 adgroup1 keyword2
campaign1 adgroup1 keyword3 etc

I've attached a document. the original data is entered on the first tab "Campaign Structure"

The button to populate the other sheets (and the macro I'm having issues with) is on the tab "DS Kwds". The keywords are being pulled but the campaign and adgroup data isn't.

thanks in advance for help


I have an excel sheet with columns of data that then needs to be transposed into rows, however each column is a different length and there are loads of columns so to do each one individually takes too long. I have managed to get the keywords over and walking through the macro it brings the top 2 titles over but the pulls down the wrong data.

Original columns look like this, with various numbers of keywords

campaign1
adgroup1
keyword1
keyword2
keyword3
keyword4
keyword5

this needs to move to the spreadsheet to look like this

campaign1 adgroup1 keyword1
campaign1 adgroup1 keyword2
campaign1 adgroup1 keyword3 etc

This is the macro I'm using. Any help really appreciated!

Sub Keyword_Transform()
Dim lngInputColumnID As Long, lngOutputRow As Long
Dim wksSource As Worksheet, wksdest As Worksheet
Dim lngColNo As Long
Dim strTemp As String

Set wksSource = Worksheets("Campaign Structure")
Set wksdest = Worksheets("DS_Kwds")

Application.ScreenUpdating = False
' Delete existing content
wksdest.Range("A7:Z7", wksdest.Range("A7:Z7").End(xlDown)).ClearContents

lngOutputRow = 7

For lngInputColumnID = 2 To 256

Application.StatusBar = "Processing column " & lngInputColumnID - 1 & " of 255"

'see if there's an Ad group title & at least 1 keyword
If wksSource.Cells(10, lngInputColumnID).Value "" Then

If wksSource.Cells(71, lngInputColumnID).Value "" Then

'wksSource.Cells(10, lngInputColumnID).Copy
wksdest.Cells(lngOutputRow, 17).Value = wksSource.Cells(10, lngInputColumnID).Value 'Campaign
wksdest.Cells(lngOutputRow, 16).Value = wksSource.Cells(70, lngInputColumnID).Value 'Ad group

strTemp = wksSource.Cells(68, lngInputColumnID).Value 'cost per click
strTemp = Application.WorksheetFunction.Substitute(strTemp, "$", "") 'remove dollar signs ($)
wksdest.Cells(lngOutputRow, 9).Value = strTemp 'PasteSpecial xlPasteValues
wksdest.Cells(lngOutputRow, 10).Value = strTemp 'PasteSpecial xlPasteValues

wksdest.Cells(lngOutputRow, 5).Value = wksSource.Cells(65, lngInputColumnID).Value 'Link URL

'Keywords
If wksSource.Cells(71, lngInputColumnID).Offset(1, 0) = "" Then
wksSource.Cells(71, lngInputColumnID).Copy
Else
wksSource.Range(wksSource.Cells(71, lngInputColumnID), wksSource.Cells(71, lngInputColumnID).End(xlDown)).Copy
End If
wksdest.Cells(lngOutputRow, 2).PasteSpecial xlPasteValues

'Engine-specific data
Select Case wksSource.Range("Engine").Value
Case "Yahoo"
wksdest.Cells(lngOutputRow, 3).Value = "Advanced" 'Match type
wksdest.Cells(lngOutputRow, 8).Value = 0.1 'Min bid
Case "Google"
wksdest.Cells(lngOutputRow, 3).Value = "Broad" 'Match type
wksdest.Cells(lngOutputRow, 8).Value = 0.01 'Min bid
End Select

If wksdest.Cells(lngOutputRow, 2).Offset(1, 0).Value = "" Then
lngOutputRow = lngOutputRow + 1

Else
With wksdest.Cells(lngOutputRow, 2).End(xlDown).Offset(0, 1)
wksdest.Range(.Cells, .End(xlUp)).FillDown
'lngOutputRow = .Offset(1).Row
End With
With wksdest.Cells(lngOutputRow, 2).End(xlDown).Offset(0, 3).Range("A1:m1")
wksdest.Range(.Cells, .End(xlUp)).FillDown
lngOutputRow = .Offset(1).Row
End With

End If
End If
End If

Next lngInputColumnID

wksdest.Select
wksdest.Range("A6").Select
' wksSource.Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub


Is there any way of removing all content in an excel document that has a certain word phrase?

I have the new excel and maybe it has this feature.

Here's a scenario, I market online and generate keyword lists with programs. I may generate a keyword list 1 day and then import it in an excel document. Then, a couple days or weeks later I will generate another keyword list under the same subject in a different way than the previous and I want to delete all contents that have certain keyword phrases like:

-cheap car rental
-cars for cheap

and so on...

The reason I'm not using the 'remove duplicate' feature is because I only want to use and see the new keywords that weren't part of the original keyword list and thus not duplicating keyword in my advertising account (PPC networks).

I hope this is clear.

Please let me know if you need any additional information on what I am trying to do.

Thanks for the help.


Hi All,

I need to manipulate existing data in a spreadsheet, so that it can be used in a data feed somewhere else. There are 2 columns, one for SKU and one Keyword. The SKU is a 6 digit number, and each SKU can have multiple words associated with it (between 0-10).

SKU Keyword
1 book
1 read
1 author
2 movie
2 director
3 song
3 composer
3 singer
3 instrument

What I need for the data feed is to present the data like so:

SKU Keyword1 Keyword2 Keyword3 Keyword4 Keyword N
1 book read author
2 movie director
3 song composer singer instrument

I think this is achievable, but not sure how to do it. I am reasonably comfortable with excel but not VBA and haven't yet done pivot tables.
Perhaps now is the time? Any advice welcome.

Thanks in advance...


Hi, let some one help me the macro code for deleting the area
that it found blank cells in the row of column B & column C & column D
my excel file data begin in column A -column G at row4-row 125
(or may be more than 125 )
In column A is the date value, that will change as any another date.
so, in the example ,the area of A84-G125 will Clear Content.(please, not delete rows)
After Deleting my data will remain A4:G83
thank.


Here's what I've got....

I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.

So for example,

(Column B)
1 - Row Above
2 - Keyword Row
3 - Row Below

If the macro finds the text in column 2, it would delete 1, 2 and 3.



I need to have a second macro that performs a very similar function -- it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.


Any help on this would be greatly appreciated!!!!


hi im new here .. i have copied a load of data from the internet into a spreadsheet but it has got the same text at the end of each cell. now i dont fancy spending the next week deleting that text by text

i was just wondering is it possible to say highlight all the cells and remove certain words??

or any other way..

Thanks david


Help with this problem please!

I have a .xls with about 1000 rows of text. The text in each cell is lengthy, running to thousands of words in some cases.

The existing text is in column A and/or column B.

I want to insert some html code {loadposition} into column A if column B is empty, or alternatively into column B if column A and B both have text.

If the html code is going into Column A I want to insert it after 75 words.

If the html code is going into Column B I want to insert it at the beginning of the text.

Any help or ideas gratefully received!

TIA

Paul


I need help.

I need to find rows (row B) with a keyword in them, and then move that row, the row before the keyword, and the row AFTER the keyword to a specific sheet.

SO for example.


Row 1 - Text
Row 2 - Contains the keyword
Row 3 - Text

The macro finds the keyword and moves rows 1 thru 3 to a different sheet.

It would also be very helpful if the macro could search for an array of keywords.

ANy help would be greatly appreciated!


I have a bunch of keywords inside my excel sheet like this:
COLUM A
keyword 1,
keyword 2,
keyword 3,
keyword 4,
keyword 5,

My question is...

Is there an easy way to make them all go into one (1) cell together like this:

keyword 1,keyword 2,keyword 3,keyword 4,keyword 5,


I am currently trying to write a VBA code to do the following:

Column A- Keyword Campaign Name
Column B - Keyword Adgroup Name
Column C - Keyword
Column D - Negative Keyword Campaign Name
Column E - Negative Keyword Adgroup Name
Column F - Negative Keyword

I have a list of Keywords (Column A) that are in unique Campaigns and adgroups, however, certain negative keywords (Column D) are preventing ads from showing because they are essentially blocking the ads because they match identically to (Column A) or are present in Column A. (Apples - Apples = blocked ad (identically matched)) (Apples - apple sauce = blocked ad (apple is present in column A and also a part of column D)

What I would essentially like to have is a VBA code which would look at Columns A & B
and compare it to Columns D & E = anything that is identical would highlight Column C if the text in Column F is identical or is also contained in column C.

I know this is a lot but I have been totally stumped on how to do this.

My current code below just looks at 2 columns: Keyword and Negative Keyword
(which doesn't solve getting it to the granular level of campaign/adgroup levels)

Code:

Sub removerows()
    
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    
    Sheets(1).Select
    LastRow1 = [A65536].End(xlUp).Row
    LastRow2 = [B65536].End(xlUp).Row
    
    For i = 2 To LastRow1
    For j = 2 To LastRow2
    If InStr(1, Sheets(1).Range("A" & i).Text, Sheets(1).Range("B" & j).Text, vbTextCompare) Then
        With Cells(i, 1)
            .Style = "Bad"
       End With
    End If
   Next j
    Next i
    
    
End Sub

Sub reset()

Range("A2:B70000").Style = "Normal"

End Sub


Anyone out there who can help????


Hi ALL

I have about 60K lines of cells that I need to remove 80 from the first 2 Values. Example as below :

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"> Machine Number
Staff
Date
80156787678



John
01/04/2010



80153000244



Jack
08/05/2010



153000556



Jim
09/05/2010



80165000677



Jason
08/04/2010




I would like to remove 80 from the first 2 value in Column A ( Machine Number). Any macro or formula that can help me to do that. We are talking about 60K of data. . Thanks


I looking for a micor can help me to remove all text after 1st comma and Remove the -,NE and spacing.I attach the sample.I would like to have a micro can remove the content column A and the results as column D.I am new in excel macro.Please someone help me in this.Thanks in advance.