I need a Macro that will search all of the cells for specific text, and delete all cells containing the text. I prefer to make a list of the specific texts and run macro to find all of them and delete all cells containing the text. I need to be able to add some more text into the list in future.
I am not the best at this, but we recorded a macro and we want to delete whole rows after we 'control' find something from a specific column. WHen we recorded our macro, it finds the first instance and we hightlight the whole row and 'control' 'shift' 'end' and delete all of the rows. We did this because we sorted and made sure the information we wanted to delete was at the bottom of the worksheet. we realized that each day the spreadsheet we pull from an ip address gets bigger and the row that we started from to delete starts on a different row each day. How do we get all the information to delete everyday, even when the row that Owned starts on changes? ...
I have a large report that I pull each month listing which products we sold last month. There are certain products I don't want included in the report. Until I can have the report re-written, I'm looking for a macro or a function that will find all the rows that contain these product numbers in column M and delete the entire row. The product numbers I need to delete are always the same, but some months one or more of them might not be in the report if they weren't sold.
In Summary:
Find in column M the product numbers: c1000 316140a 316140 316295a 316295 316311a 316311 316451a 316451 316450a 316450 316452a 316452
Delete those rows containing the product numbers above
the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.
every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.
I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.
What i have is a workbook that can have multiple sheets. Sheet 2,3,4 (if there are) are a continuation of sheet 1. First 2 rows of sheet 1 are headers and the last row of the last sheet (can be 2,3, 4....) is a footer. There are 14 columns (A-N) in each sheet. Col "N" has set status and col I has set names.
What i am looking for is a macro that can perform the following actions in the the set order: 1) deletes all rows where status in col N is "closed" 2) delete all rows where name in Col I is "Many" 3) if there are multiple sheets copy all data in 1 sheet once above 2 are deleted 4) I want to update cell A2, C2, I2, M2 & N2 with Hostel, Direction,Name,Flight & Status respectively 5) Create a pivot table with all data (Col A-N & Row 2 to last) in a new sheet and call it Summary - Pivot I can then manually select how the pivot will look
Currently i am going through all sheets manually and filtering data. Using excel 2007
I'm trying to create a macro that will look at each worksheet in a workbook and then delete the last line of data on each worksheet. The last row can vary on each worksheet. This is what I have come up with but it is not working. I am on Excel 2010 and Windows 7.
I need to have my macro select all buttons in the current row (including the button I clicked on and delete them. There are many rows with buttons. Each row needs to have this button. So I need it to be row specific.
I know that this is how to select the row that the button is in:
But I do not know how to select the buttons in that row. It needs to be included in this:
VB: Sub move() Dim shname As String, sh1 As Worksheet, lc As Long, rw As Long Set sh1 = ActiveSheet lc = sh1.Cells.Find("*", , , , 2, 2).Column rw = sh1.Buttons(Application.Caller).TopLeftCell.Row
[Code] .....
After many attempts and alot of research, I have some more code for this problem: This deletes all buttons in the sheet:
VB: Sub DeleteShapes() Dim shp As Shape Dim myVar As Shapes ActiveSheet.Activate
[Code] .....
And this deletes the current button only:
VB: Sub pressbuttons() With ActiveSheet.Buttons(Application.Caller) .Delete End With End Sub
Any way to either limit the first code to just a particular row, or expand the second one to the entire row.
I need to build a macro which will look for a specific word say :"ABC" in a particular column say "B" and delete 5 rows including the row containing the word "ABC"
I was searching for a macro to delete rows that obey a specific criteria. I downloaded [URL]..... and copied the Loop Example to my WorkBook.
When using Mr. de Bruin example, it worked perfectly, it did really delete the rows that obeyed my ">2" criteria. Although, when using on my workbook, the macro deleted the values, not the entire rows. Here's what's happening:
I would like to delete rows, not only values.
Code:
'The code below will delete every row in the usedrange with "ron" in the A column. 'If .Value = "ron" Then .EntireRow.Delete ' 'I use the A column in my example, change the A to your column in this code line. 'With .Cells(Lrow, "A") ' 'Change ron and the A to your search word and column before you try the macro. 'See the examples on the worksheet if you want to do something different. Sub Loop_Example() Dim Firstrow As Long
I currently had to start from scratch as my formula kept raising the debug screen. I know the answer is simple but I cant figure out what it is. I have column A, rows 9 through 1000 with data. I want to delete the entire row of any "x" value present in column A rows 9 through 1000. I am new to VBA. So simple is good.
I am having some trouble writing a macro to delete cell content. I know which cells I want to clear, but I can't seem to get the macro to do it.
I also want to have something in the macro that inserts data into the lines that I am trying to clear. I want cell A67 to say "Payment" and cell A71 to say "Total."This is the code I am using: Sub DeleteCell() Dim i As Integer For i = 67 To 71 If Range("A" & i).Value = "--" Then Range("A" & i).Delete Next i End Sub
I am looking for a macro or someway that I can delete data out of a workbook that dont contain specific data. On the example shown below, if in column E it dont say one of the following Nhampton, euston, tring, bletchley, Nhamp Emd, Nhamp NJ, Nhamptn RS, Watford Jn, Bltchly MD, Bedford, Bletch CS or M keynes I need the row totally deleted.
******** ******************** ************************************************************************>Microsoft Excel - Book1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA4= ABCDEFGH4LTP**BY**751*******5*CNDR**CRE****6On4.472*Bletchley*04+595K087Off11.47**M*Keynes05+055.242K088Hrs07:00**Euston6.296.372N139****Nhampton7.53**10DaysSX**TRAIN*GOES*FORWARD*TO*NEWST.***11From09/07/2007422(350*Nhampton*8.241W5212To07/09/2007**Euston9.25**13***PNB(*30**MINS*)***14***ASSTEuston*10.242N5715****(To*Northampton)***16****Bletchley11.12**Sheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I need a quick macro that will search column D for the words "balance forward" then delete the entire row. The macro needs to loop through many rows and it contain more than one "balance forward" reference.
I have been trying to create a macro vba that will look at a specific row in the current sheet and if it sees a name I need to delete that row. But I am looking to also under stand what it is doing so I can use this script in other instances. I just bought some things off MrExcel's Store but i won't get this for a few days.
I have an excel workbook where some sheets have a column called "Name" in Row 3. The column where "Name" appears shifts based upon other criteria so it's not set within 1 specific column.
The real data for the "Name" column starts in Row 5. Is it possible to create a macro that looks in Row 3 for "Name", then once it finds that column, it does a find and replace from Row 5 to the end of the data and replaces every space with a ^.
For example, if I have:
Row 3 Name Row 4 Row 5 John Smith Row 6 Jane Doe Row 7 John Doe Row 8 Jane Smith
I have tried to record this but I cannot get it to work? On the tab “Add & Delete” cell “L17” I have a drop down list that is a list of the policy numbers from the “Data” tab.
What I am trying to do is assign a macro that searches column “J” on the “Data” tab based on the “L17” content on the Tab “Add & Delete” when it finds the data it then clears the contents of that entire row.
I've got a VBA macro set up in one Master excel file that manipulates a number of other files. Both the Master and the other files are all in a folder which is currently called C:Documents and SettingsusernameDesktopPipeline and my VBA script has this defined as the filepath and works exactly as I need it to. I will be moving the folder "Pipeline" to a shared drive on my server so my staff can access it, and will be sending it to colleagues who will be installing it on their own servers so they and their staff can use it. Is there a way to amend the code so that the VBA in the Master file looks in the folder in which it is locate, "Pipeline", no matter what the filepath leading to the "Pipeline" folder is?
I'm looking to simply delete certain words that are in cells, without having to delete rows, columns or the entire cell. I can use 'find and replace' and replace the particular word with a space, but I really want to delete the word. I'm using both Excel 2003 and 2000.
I have two spreadsheets. one contains a master list of computer names. the other contains a list of computer names to be removed from the master list (exception list).
I need to go through the master list and remove any computer names that are on the exception list- for example, if 'computer1' is in the exception list, i have to find and remove 'computer1' from the master list.
The exception list is quite long, and I want to automate this process if possible. not sure how to achieve this.
Following the tips doesn't allow to remove a character.
I exported email addresses from Outlook and they have the following character ' in front of the email address.
Using Excel 2007.. it says :"check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected worksheet. Excel cannot replace data on a protected worksheet"
So I made a new file, and copied and pasted the cells into the new spreadsheet.. same message.
In column B I have a list of blank cells and cells that contain values. I want a macro to search for 'Jobs' and if it finds this word in column B then delete the entire row where it is find and also delete the row below it if column B is blank and also delete the row below again if column B is blank (up to 8 rows below where the word is find at most will be blank)
When it eventually finds a cell that is not blank, then exit sub.
Why my code is not working. I am working with Excel 2010. It will only delete the column on the active spreadsheet and not go to next worksheet. Not all worksheets contain the word "Broker".
[Code] ......
Not sure if its something to do with Activecell, try After:=ws.cells(1,1) or passing it in as the active cell stays the same i think.
After much fretting over this (and many other Excel issues), I've decided to seek out some assistance. I've included an example with columns, because I'm not very fluent in Excel terminology.