Deleting Cells With Keyword Macro?
Jul 23, 2014
So I have data that I'm importing in rows 1,2 and 3. The first row has the product type, then rows 2 and 3 have additional info pertaining to that product. I was wondering how I would write a macro that could identify entries in the first row that equal a particular product, and remove those cells, along with its respective info in rows 2 and 3. Ideally, I could just push a button, and a particular product along with all it's info would disappear from my imported table.
View 4 Replies
ADVERTISEMENT
Mar 15, 2009
I would like to have a macro to loop through all the worksheet except for "Summary" worksheet.
The macro will look for the keyword in the worksheet starting from row 2 from every sheet.
if the keyword is found on the row, the entire row will be deleted and shift row up.
Keyword for example is found in H13 in the Summary worksheet
Summary
GH13KeywordLondon
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Mar 9, 2014
I'm looking to identify a keyword in a string and then replace that string with just the keyword.
The string is a product description. The keyword is a product group. The keyword can showup in any position in the string.
I can't post the actual data do to confidentiality requirements. but here's an example.
description
qty
price
keywords
code
green grapes bunch
1
2.5
[Code] .......
View 2 Replies
View Related
Jun 25, 2009
I need to search column C for the text "X". If an "X" is found, I need to copy the text from the cell directly left of it and paste it into a seperate worksheet into cell B2, then go back to the original worksheet and delete the cell with "X" on it and the cell to the left.
View 9 Replies
View Related
May 25, 2007
I have a macro that works fine in one workbook. I copied it to use in another one, but then I get this error message: "Invalid Use of Me Keyword".
Dim wSheet As Worksheet, strMsg As String
For Each wSheet In Worksheets
If wSheet.ProtectContents = True Then
strMsg = "All sheets protected."
Else
strMsg = "All sheets unprotected."
End If
Next wSheet
MsgBox strMsg
Unload Me
End Sub
View 7 Replies
View Related
May 10, 2014
I have a list of drugs which possibly could increase to thousands in rows. I will need a macro that can scroll down to a particular row when the text in that row has been entered.
Assuming I have LOTEMP in A5000 , I would want a macro to scroll to row 5000 when the word , " LOTEMP" is typed in cell , say B2.
View 2 Replies
View Related
Jun 8, 2007
I have a large keyword list in a sheet called "AllKWs" In ColA from A3 downwards).
What I'd like to be able to do is this, which I'm sure will be complicated, but I will explain.
Say keyword phrase sheet has 25,000 rows of data (could be more/could be less).
I click an assigned macro button.
A pop up box appears.
I type in a word or words I'd like some info on, so for example I type in a word or words like "car rent"
It then returns for me on a new sheet called "Multi Keywords" a lot of data on this sheet, which would hopefully be as follows:
All Row 1 will contain Column headings
All row 2 will contain Total Counts (I'll explain in a minute this row)
So, all data to be returned from Row 3 downwards.
OK, as to the data to be returned.
All returned data In all Columns to show data in descending order by No of occurrances/appearances
Col A (From A 3 downwards) = The actual number of 2 word appearances (In this example that contain the words "car rent"
In Col B = All 2 word Phrases Containing ("In this example "Car Rent")
(As a note, In this example, ColA (CellA3) could only show the number"1" and ColB (B3)could only show the phrase "Car Rent") once. (As there isn't no other possible combination).
In Col C =The actual number of 3 word appearances listed in descending order That
contain the word "Car rent"
In Col D =All 3 word Phrases Containing "Car Rent"
In Col E =The actual number of 4 word appearances containing "Car Rent" listed in descending order
In Col F =All 4 word Phrases Containing "Car Rent"
In Col G =The actual number of 5 word appearances listed in descending order
Actually if anyone can crack this I really do take my hat of to them.
OK,A few more points,
Cells B2,D2,F2,H2,J2,L2,N2,P2,R2, All contain the word "Total:" and if the macro can fill in the number as appropriate.
So for example Cell L2 (For 7 word phrases) would say something like "Total:42" (If in Col L From L3 downwards the macro found 42 7 word phrases that contained the words "Car rent"
Ok, Cells A2,C2,E2,G2,I2,K2,M2,O2,Q2 All these cells will contain the word "Total". So these cells would list the combined total number of occurrances of all the phrases.
So for example cell K2 might say "Occur:324" as the total number of occurances of 7 word phrases that had the words "Car rent"in.
OK. as an example, I will post a code that Jindon wrote for me sometime ago. I'm posting this now, as it is very similar in what I would like this macro to be able to do, and might help as I'm sure this 1 will be complicated. This 1 looks for a phrase, returns by No of occurrances etc, but for all the combinations (Word lengths) within the Keyword phrase list, rather than what I'm asking for now, which splits them into Number of words columns.
Here it is anyway:
Sub NicheKeywordFinder()
Dim a, dic As Object, X, myTxt As String, b(), c(), n As Long, i As Long, e, s, myTotal As Long
myTxt = InputBox("HuaHinCarRental - Niche Keyword Finder") 'change to suit
If Len(myTxt) = 0 Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
ReDim b(1 To Rows.Count, 1 To 1): ReDim c(1 To Rows.Count, 1 To 3)
With Sheets("All KWs") 'change to suit
a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
View 9 Replies
View Related
Nov 4, 2013
I have a workbook with several sheets that have basically the same template but some have many more rows that others..
I am using the following code to hide columns based on cell value to "tidy it up" for printing.
Code:
Dim i As Long
For i = 170 To 2 Step -1
Cells(39, i).EntireColumn.Hidden = Cells(39, i).Value = "N"
Next i
I would like the VBA to determine what the row number is based on the row header e.g. "Prioritised Courses", rather than having to maintain the code each time new rows are added.
I would put it at the top or way down the bottom, but multiple (even less excel skilled) users will be using the "hide columns" functionality.
View 6 Replies
View Related
Apr 3, 2013
So basically I have an Excel sheet which has keywords that need to be entered in Google search. I need the URL of the first page of the search result that appears after that keyword is entered. IS there a macro for the same?
View 3 Replies
View Related
Sep 24, 2012
I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
[URL] ......
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
View 2 Replies
View Related
Aug 15, 2007
The loop cycles through my sheet correctly but wont delete any cells.
View 10 Replies
View Related
Apr 30, 2014
I have a data in which I have inserted a table with headers.
I then filter the type of data I want removed from the table using the drop down
Then goto special, visible cells only, press delete.
Now, the data is gone, but when I select all again to pull up the entire table, the data is gone, but rows are still there. So now I have tons of empty rows in between the rows of needed data. Any way of removing the rows in between as well as the data?
View 4 Replies
View Related
Jul 7, 2014
I need to store some numbers, all of which are prefixed by four or five zeros. When I format the cells as number, the zeros are erased. So, 0000012345 becomes 12345. The zeros are preserved when I store them as text, but I cannot do the functions that I need to do with the cells formatted as text.
How I can preserve the zeros in number format?
View 2 Replies
View Related
Feb 3, 2009
When you highlight a group of cells and give them a reference name- that name seems to be permanent. How do you delete one or add cells to the reference? Example attached book1.xls Cells A1-B3 are named "TheGroup".
View 2 Replies
View Related
Mar 29, 2009
I have a column with "1" and "0". I'd like to have something like, if the value in a cell within the column is "0", then delete the 4 cells previous to the cell that contained "0" within the row.
Instead of deleting, the values can also be replaced with anything e.g., zero or n/a etc.
View 4 Replies
View Related
Apr 14, 2009
The delete function has disappeared from my EDIT menu in excel. Delete and Insert has also disappeared when I right Click on the spreadsheet.
View 11 Replies
View Related
Dec 13, 2006
i have worksheet with peoples details in it... i have a userform with a combobox and a delete command button in it with the persons name in the combobox... and i cannot figure out how to delete the persons details with the combo box...
View 9 Replies
View Related
Sep 22, 2007
Is there a way to delete empty cells, without having to do each one individually. I have 10,000 numbers with about 500 empty cells. I did a search, but found nothing...
View 9 Replies
View Related
Sep 1, 2008
I am trying to a method to delete rows that contain EL or LE in a specific cell in a set column as this will reduce my file down by nearly a third. Currently by doing it using a autofilter it kills my computer as there is so much data. Is there an easy way to reduce this?
Trade ID456465ELColumn B456456LE Column B8454321LEColumn B456456ELColumn B5464564LColumn B4564546LEColumn B
View 9 Replies
View Related
Jun 12, 2009
I need to have my macro select a certain range of cells with a specific product code and delete them. What would be the code for it (product code 107). Here is what i have so far:
Workbooks.Open Filename:=fNameAndPath, UpdateLinks:=1
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath2 & myFile3 & myExt, FileFormat:=xlNormal, CreateBackup:=False
Windows(myFile3).Activate
Cells.Select.Range ("A78:K153")
Selection.Delete
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath4 & myFile4 & myExt2, FileFormat:=xlCSV, CreateBackup:=False
View 9 Replies
View Related
Feb 27, 2007
I am trying to write a macro that looks at columns C-I in row 6, to check if the value is G, if it is then I want it to delete C5,6,7 & 8 and so on along to coumn I. So all that I am left with at the end of the macro are the values that weren't green without any blank gaps in the columns. I started using the following
If C6 = G Then
Range("C5:C8").Select
Selection.Delete Shift:=xlToLeft
End If
And using that same code for D6, E6 etc etc, but obviously, when it deletes the cells it shifts them left so the columns change values, so if C6 is equal to G then it deletes it but then what was D6 becomes C6.
View 2 Replies
View Related
Jun 29, 2007
I know this is an easy question....what syntax would you use if you want a macro to search Column A for every cell with specific text, and then clear that cell? I can't seem to get the syntax right to get it to do what I want.
View 2 Replies
View Related
May 2, 2014
I am trying to write a macro that takes the value in cell B2 from a workbook called ""numbers" in a worksheet called "summary," and creates a new worksheet in a workbook called "filestransfer" that is named the same thing as the value in cell B2 from "numbers." That part is done.
View 2 Replies
View Related
Mar 19, 2009
I want to delete every second row, because I have data with 0.25m interval and I want 0.5m interval. Why I try the following it doesn't work?
View 2 Replies
View Related
May 8, 2012
I need a simple macro to delete all sheets in a workbook except one that is called "START"
View 9 Replies
View Related
Jul 25, 2012
I'm looking for a macro that will delete a row with a certain value (0.00) and the row below it.
View 9 Replies
View Related
Aug 23, 2012
So I have a macro that creates a monthly set of reports, but some of them only relate to period 5 onwards. I wanted the macro to check whether a folder exists for periods 1 to 4, and if so, delete the folder and all the files inside it. I've used the following...
Code:
If Period < 5 Then
If Dir("I:AcctsCentral EngineeringEngineering Reporting Pack2012-131 April 20122-2 Reports - 4+8 Reforecast") "" Then Kill "I:AcctsCentral EngineeringEngineering Reporting Pack2012-131 April 20122-2 Reports - 4+8 Reforecast[code]....
but the macro just skips straight through the secondary IF statements without stopping.
I know this works on deleting files, and presumed it did too for folders, but it doesn;t.
View 2 Replies
View Related
Oct 19, 2013
I'm trying to delete both rows of the same data not just one.
item
warehouse
location
PANT
7050
2
A1-05-89
PANT
5051
2
B1-19-19
SHIRT
7050
2
J1-08-67
SHIRT
7051
2
J1-08-67
What i need is if there is a same location delete both rows and i cant figure it out to get it to do both of them not just one row.
View 9 Replies
View Related
Jan 2, 2014
I have a problem with the following macro:
Code:
Sub CalcsDelete()
Dim count As Integer
count = 1
For count = 1 To 100
ActiveSheet.Shapes.Range(Array("Picture " & count)).Select
Selection.Delete
count = count + 1
Next count
End Sub
It should simply delete picture 1, picture 2, picture 3, picture 4, etc. The problem lies within the "Picture " & count part.
This is obviously the name of the picture, i.e. picture 1, picture 2, etc.
That's what I tried to accomplish with the for-loop, but I'm not sure how to increment the number of the picture by means of using a for-loop and having that be the new name for the picture.
View 6 Replies
View Related
May 20, 2008
how to remove a macro button from the sheet after we've used its functions.
For example, i have a macro that emails the workbook. Once the emailed workbook is out, i would like the macro button to be removed after we press it.
View 9 Replies
View Related
