I have a excel file with more then 10 sheets..Some of the sheets contains this word in some random cells" #DIV/0! " I want a macro which can find it in every sheet except parent sheet and can remove it.
View 5 RepliesI was wondering if there is a formula or command to find and delet specific rows. I want to remove people from an excel email list.
On Sheet 1, I have 500 rows with columns for first name, last name, and email.
On Sheet 2, I have 30 rows with columns for first name, last name, and email that appear on Sheet 1 but need to be removed.
Is there a way to do this without manually searching for each email and then deleting the row?
I want to delete a row in a spreadsheet by finding the row that contains a particular value. I use MATCH on spreadsheet #1 to identify the row number on spreadsheet #2 to delete. I need the macro to select the row number on spreadsheet #2 and delete it.
I don't know the syntax for the commands in VBA to write this and I'm coming to this forum for help because I'm too lazy to learn VBA. I've recorded macros to add rows, populate cells, hide sheets, etc. The following code is extracted from a macro I recorded to try to get started, and was hoping to be able to substitue a reference to sheet1 within the parentheses at
Rows("4:4").Select
but I can't make this work. The following is the code I was trying to adapt.
Sheets("Sheet2").Select
Rows("4:4").Select
Selection.Delete Shift:=xlUp
End Sub
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"
View 6 Replies View RelatedThis Macro is supposed to get certain totals for me from diffrent pages. Instead of selecting an entire row I want to select specific cells, so when it finds the word total on the sheet, whose location can change often it will return the value two cells away.
View 3 Replies View RelatedI have a large spreadsheet with about 18000 rows or data and about 60 columns. I need to a macro to find a specific word in a column like "charge" and anytime that word is found in the column to insert a blank cell in front of it.
Currently the sheet looks like this:Fee AChargeFeeChargeFee AChargeFeeChargeFee AChargeFee AChargeNeed to get it to look like this: (so I can sort and subtotal)Fee AChargeFee AChargeFee AChargeFee ACharge
a macro to the for whichever column I choose.
I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"
My code if needed is:
Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub
I would like to have a formula find a specific word in column L and return the sum from column E for the same fund from column C.
I tried using =SUMIF(L:L,"*annual*",E:E) but that gives me the sum of annual for the entire column. I need to be able to specify the fund.
word "annual" also appears in "semi annual" so I need to be able to separate the two.
I am trying to run a macro once a particular word shows up in the combo box. now in my chase i could try to use VBA code to run the macros such that once the arrow is used in the combobox to select a certain word a particular macro labled the same name as the word chosen would run.
View 5 Replies View RelatedI'm looking to do a search and delete in Excel 2007 and I'm having a great deal of difficulty trying to do this. I've attempted to modify some code I found on the internet and not having very good luck with it. Here is the scenario, I've got a spreadsheet with 5 columns (A to E). In column C, there is a product name with certain identifiers that set it apart. An example of this product name with identifier is "product XXX_type 2_attribute ". I want to search for "type 2" in Column C of each row and then delete the row.
Here is what I have written so far and I'm not having any luck.
Sub RowDel()
Dim cell As Range
For Each cell In Range(Range("c4"), _
Range("c65536").End(xlUp))
If cell = "_GPnl_" Then
Range(cell, _
Cells(Rows.Count, 1)).EntireRow.Delete
Exit For
End If
Next cell
End Sub
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C
Notice the two examples in the excel file.
Excelforum.xlsx
I currently have the following code that works great and deletes the row from my spreadsheet when the word "vacant" appears in column F.
Sub test()
Dim r As Range
With ActiveSheet[code]....
The problem is, "Vacant" also sometimes appears in column G. So, as this code is now it is finding the word "Vacant" in either column F or column G and then deleting the row.
How can I write the code so that it only deletes the row where "Vacant" appears in column F but ignores it when it is in column G?
I'm trying to automate a redundant task. I have several Word files in one central folder. I need to open each file, remove all paragraph marks (via find/replace all), copy the result, and paste it into an Excel sheet. This process will be repeated for each file in the folder.
The code I have is almost there, however, I can't get it to do the find/replace. Below is an extract of my code for the Word application part. The code for the copy and paste will be inserted after the replace action works:
Dim Wapp As Object
Dim i As Integer
WorksheetsLocation = TextBox1.Value ' this is the folder location brought in from an input box
On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number 0 Then 'Word isn't already running
Set wApp = CreateObject("Word.Application")
End If
On Error GoTo 0
I had alot of cells that has replication in the same cell. I would like to find out if the cell has any replication. If there is, delete the repeated data if not go to next cell
Example,
Before
Peter, John
Peter, John, John
Peter, John, Peter
John, John, John
Peter, Peter, Peter
After
Peter, John
Peter, John
Peter, John
John
Peter
[edit] The example above is 1 cell per line [/edit]
Any way of finding particular data from a data set and deleting the data from that field without using conditional formatting or remove duplicates?
I have used a vlookup to find the words that I needed I now need to find where the are located in my original data set and delete these so I am left with data that if I perform a vlookup on it will not bring back any words as they will have been deleted.
I deal with leads for a sales room and get sent over leads in bulk, I've created a master scrub list that I can attach to the end of a new lead file and sort by number to show which are duplicates.
When you do the: Data, Filter, Advanced Filter, select Unique Records, it hides the duplicate but what I need is not only for the duplicate to be hidden or gone but the row that it is a duplicate of, i.e. I need BOTH rows to go
Name-----number
Dave 555-1212
Dave 555-1212
John 536-2343
Smith 423-2312
needs to become
Name-----number
John 536-2343
Smith 423-2312
I would need a formula that figured out that Dave with number 555-1212 was a duplicate and delete BOTH rows,
I have a large keyword phrase list.
In a sheet called "AllKWs".
What I would like to be able to do is find all Country and town names in that list and delete the names only in the phrase, not the whole row.
Several points though.
Can the macro (As I will assign a text button to it).
Only find and delete these words if they are either at the start or end of a phrase?
4 example phrases; ....
I am trying to create a search function in a database I have made. I have a userform with drop down boxes that people can choose what they want to search for. Once they have chosen and clicked the search button I want it to search only in the relevant column, i.e. search for the word in the dropdown for "engine" only in the "engine" column of the database. Once it has found the appropriate cells with the answer, I want it to delete all other rows that don't include the answer.
Once it's done this it needs to do it again for the other fields, unless they are empty. I have used 'If engine_type <> "" Then' as the starting point so that it only searches chosen fields.
I'm trying to create a macro that will look in each row and find the first nonzero that it contains, and then proceed to delete all prior cells before it and shift the existing cells in that row to the left. Now I know that the formula to find a non-zero in a row is:
=(MATCH(TRUE,INDEX(B9:BE9>0,0),0)-1)
and I figured that to select all cells before it you would simply use:
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Delete Shift:=xlToLeft
My problemms are:
I can't figure out how to translate that formula into a piece of the macro and also instead of having it simply calculate how many zeros there are until the first nonzero to get it to select that first nonzero
How to make the selection conditional upon the row. At first I thought maybe I could just have it paste the formula into the first cell of the row but I realized that it wasn't possible to do as the formula already has the row selected
How to have it select all the prior cells to the first nonzero, delete them and shift existing cells to the left without deleting the first nonzero.
i have a set of words in sheet1 column A, in sheet2 columnA, i copied the words and type the meaning on columnB. in sheet1, i have a command button adjacent to the word in columnA, when i click that button, it will go to sheet2 with the same word beside the button in sheet1 columnA, so that the user may see the meaning of the word in sheet2. is it possible to generate a code like that?
View 9 Replies View RelatedI have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
I have many worksheets which ends with the word " data". Is there a way to delete all the data sheets at one go through VBA code.
View 3 Replies View RelatedIn column A we have words, like this:
CHAIR
EXCEPT
WORLD
STREET
OUTSIDE
PEOPLE
WINDOW
LETTER
BOOK
In column B we have another list of words:
NIGHT
DREAM
EXPECT
THOUGHT
TEDIOUS
OFFICE
SETTER
LIBRARY
BEFORE
OFFER
I need a formula in C1 which will check all the words from column B and find the ones which are anagrams of some of the words from column A, and then return those words next to them.
(Anagram is the word made up of another word's letters, without repeating or omitting any, for example anagram of SPARK is PARKS).
In the example above, we would have the words: EXCEPT next to EXPECT (in C3), OUTSIDE next to TEDIOUS (C5), and STREET next to SETTER (C7).
I want a formula to scan column B for a given word. One version of the formula will look for the word "ACAD", the other "GOV". If it shows ACAD anywhere in column B, I want to put ACAD in column D.
View 10 Replies View RelatedOn sheet 2 in cell C5 I need it to find it from sheet 1 referring it from the word "Drinks 1" thats in Q1 and so on along the row
View 9 Replies View Relatedif you can remove the word total from each cell without deleting the numbers after you copy from a subtotal list?
View 9 Replies View RelatedHere's my problem. I have the following table:
View 5 Replies View RelatedI have created a worksheet (through an import into MS Excel 2007) which contains 287,281 rows. However, the data I need is located in rows: 4, 67, 130, ... (or n+63) rows.
View 10 Replies View Relatedhey everyone
i'm using a table like this one :
A B C D
1 Main | value | value | value
2 Extra | value | value | value
3 Main | value | value | value
.
.
n Extra | value | value | value
i want to delete every cell in column "D" only when cell in column "A" contains "Extra" value.
I have Column A with 380 cells filled in with numbers (A1-A380) i.e. numbers like..456758, 567848 etc etc
Column B has a subset of those numbers, maybe 80-90 or so (B1-B90).
What I want to do is either highlight the numbers in Column A that also appear in Column B in yellow, OR delete them from Column A altogether