Clearing Cells Containing Specified Words
Apr 29, 2008
I am trying to clear the contents of cells in a very large spread sheet containing certain words. I am running the following macro and it will only do the first part, so it will clear cells containing "deceased" but stops at "esa". There may be any number of other words or text in the cell and I want it to delete cells with "esa" by itself so for ex. not delete cell containing "vanesa". I am very amateur so I am sure this is something simple but I cannot find the answer or figure it out for myself.
Sub DeleteAll()
Dim lCount As Long
Dim rFoundCell As Range
Dim rLookRange As Range
Set rFoundCell = Range("A1")
Set rLookRange = ActiveSheet.UsedRange
For lCount = 1 To WorksheetFunction. CountIf(rLookRange, "*deceased")
Set rFoundCell = rLookRange.Find(What:="deceased", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
rFoundCell.ClearContents.......................
View 6 Replies
ADVERTISEMENT
Aug 1, 2008
How do I use this to delete the row instead of just the cell?
Sub DeleteAll()
DeleteCellContaining ActiveSheet.UsedRange, _
Array("deceased", "dcsd", "decd", "dec'd", "fcc", "dtd"), _
xlPart
DeleteCellContaining ActiveSheet.UsedRange, _
Array("esa"), _
xlWhole
End Sub
Sub DeleteCellContaining(Data As Range, Items As Variant, CellMatch As XlLookAt)
Dim rngFind As Range
Dim vntItem As Variant
With Data
For Each vntItem In Items
Set rngFind = . Find(What:=vntItem, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=CellMatch, _
SearchOrder:=xlByRows, _.........................
View 4 Replies
View Related
Jul 19, 2002
I have a spreadsheet that has I have protected. On this sheet I have a button labeled Reset Form. When you click the button it it goes to each Unprotected Cell and deltes the contents. I created this by starting the macro and then tabbing to each and every cell and deleteing the contents I then ended the macro...
Is there a code that I can use for to clear all unprotected cells as opposed to creating the macro manually?
View 9 Replies
View Related
Apr 27, 2007
My computer runs on Excel 2003 but my Laptop is on Excel 2000, the problem is I have the below code on a spreadsheet I wrote on my Computer which runs fine but when I open it on my Laptop it comes up with a run time error it doesn’t like the clear contents line. If some one could explain to me why it is happening and how to get round the problem so it will work in Excel 2003 and 2000.
Private Sub Workbook_Open()
Application. ScreenUpdating = False
Sheets("data").Visible = xlVeryHidden
Sheets("Incidents").Visible = xlVeryHidden
Sheets("Front Sheet").Select
Range("A17:S17").Select
Selection.ClearContents
Open1.Show
Range("A17").Select
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
Aug 10, 2014
I have cells, or columns hidden when I do not need them, but I have to a data entered in those cells to make my spread sheet work. Usually just 0.
When I do the all clear or " clear all contents" operation at the end of the day, it clears the hidden data/cells too. Is there anything I can do to stop excel from clearing those hidden cells?
View 3 Replies
View Related
Mar 25, 2009
I have a macro where somone fills in a form and then it copies the data in the form to a database (another worksheet). Once the macro is run and has copied the form to the database, I then clear each cell seperately using the below code :-
View 5 Replies
View Related
Oct 15, 2013
I have compiled the following code with bits from here there and everywhere to clear the user entered details on a form. My problem is that I need to retain or reset to "Please Select" the cells with drop down lists.
In list form, I am trying to;
Unprotect the worksheet,
Select the cells with drop down lists and lock them,
Delete the contents of unlocked cells,
Selecting and unlocking the cells with the drop down lists ready for re-use,
Re-protect the worksheet.
(I probably should mention that my "Form" is just a formatted worksheet that looks like a form on the screen and when printed, not any sort of inbuilt Excel function that I have read about somewhere, probably here)
Code:
Sub ClearUnlockedCells()
'
' Clearform Macro
' Clear entered data from Form.
'
Dim WorkRange As Range
Dim DVRange As Range
[code].....
View 4 Replies
View Related
Oct 3, 2008
I've created (with the help of jmthompson from here) a macro to pull an entire row of data and copy to another sheet (Sheet1) if column U has "YES" in the cell.
Now I need to clear the contents of cells in range B-U after the copy has occurred.
Here's the current macro: ...
View 9 Replies
View Related
Dec 7, 2009
I created a macro to clear cells but I can't get it to clear a number of ranges. It will only clear single ranges.
Sub DeleteStuff()
Dim c As Range
For Each c In Range("G6:G10", "H6:H10", "J6:J10")
If c "" Then c.ClearContents
Next c
End Sub
View 9 Replies
View Related
Dec 6, 2007
I have a workbook with a series of sheets that have tables for entering data. The table row and column labels and formulas in certain cells of each sheet are locked and some sheets are hidden.
When opening the file I want to clear all cell contents (interior color, comments, data, etc) in all the unlocked cells on each sheet that is not hidden.
I tried protecting the sheets first so only the unlocked cells would be accessible, but when I run the code below I get an error saying that the clear contents etc. cannot be performed because the sheet is protected. If it is not protected, everything gets wiped out.
How can I keep the locked stuff but clear the unlocked cells?
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ws.Protect Password:="aaa" 'Protect each sheet
ws.Cells.ClearContents 'clear content of any unprotected cells
ws.Cells.ClearComments 'clear any cell comments
ws.Cells.Interior.ColorIndex = 0 'set background colour to no fill
Active.Cells.Range ("a1") 'make the active cell the top left
End If
Next ws
View 9 Replies
View Related
Jun 2, 2014
I have a target range for a worksheet change. Then when finished I highlight the data and press 'delete' I get an error within the code.
View 2 Replies
View Related
May 30, 2009
Code to run a macro called "PriceForm" instead of clearing the cells.
Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("T5:X50").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
View 9 Replies
View Related
Oct 14, 2008
Sub test()
Range("B30").Copy
Range("A30:B30").ClearContents
End Sub
Problem: the clearing of the cells clears as well the copying of "B30"
View 9 Replies
View Related
Jul 27, 2009
im currently wonderin if the SUMIF function can have a WHERE syntax?
because i want to add up cells which contain these:
A B
1 VC-ON US 2
2 MC-ON US 5
3 MC-OFF US (STD) 8
4 VC-OFF US (PREM) 9
in another separate cell, i want to retrieve the sum of numbers with the words ON US in column A. so the final result will be 7.
View 9 Replies
View Related
Sep 20, 2006
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies
View Related
May 5, 2009
I have a list of names, some have first name and surname (in the same cell), and some just have first name.
Is there anything I can do to only show the cells that contain a first name and surname?
View 7 Replies
View Related
Feb 13, 2009
I'm looking to take words which are originally in their own separate cells and "combining" the words all into one single cell.
Is there a function that will be able to do this for me?
View 2 Replies
View Related
Jun 23, 2009
I have hundreds of file names, and to cut a long explanation short, they are exported as a CSV file to excel. In order indentify the owner of the file I need to rename the file with a commar where you see the dash or the underscore in order to have the name appear in the next cell, as can be seen with Syma and Kevin below, so I can then sort the columns etc etc.
I know there is a formula that I can use to achieve this, ie have syma.pdf and kevinc.pdf in the next cell.
I think it works on identifying how many characters along the name and then send i to the next cell.
as you can see below, there is no set amount of characters to put into a formula.
Could I say, for example, if after dash /underscore put next word in next cell?
t5 mon a2 w6-syma-0003.pdft5 mon cst w2_dellwynneh.pdft5 mon a23 w3_MASUMS.pdft5 mon cmn201a w1- syma.pdft5 thurs wiabe w2_kevinc.pdft5 thurs wiabe2 w2_kevinc.pdf
View 9 Replies
View Related
Aug 13, 2007
i have a column where the sponsor has a name of a race and i wish to remove the sponsors name and replace it with something of the sentence
for example
Barton And Guestier Top Novices' Hurdle Class A Grade 2
i wish to just locate the Novices' Hurdle part and change that particual cell to nov hurdle without the sponsors name
is it easy to extract this info
View 8 Replies
View Related
Jun 3, 2014
I'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
View 7 Replies
View Related
Jul 16, 2009
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
View 9 Replies
View Related
Jun 12, 2014
All words are in Cell A, separated by the ";" symbol. I want separate into different Cells. Example... Lead_ID in Cell A1, Application Data in A2, Date Purchased in Cell A3 and so on.
lead_id;application_date;date_purchased;first_name;last_name;email;day_phone;work_phone;
best_time;city;state;zip;addr;cred_rating;cred_score;yearly_income;prop_state;prop_type;purpose;
loan_amount;prop_value;ltv;mtg_bal1;mtg_bal2;services;credit_repair;foreclosure;debt_management;comments
View 5 Replies
View Related
Oct 31, 2008
I have database of customers and one collum is labeled "Name and Family name". I want excel to split this records in two collums, ergo one labeled Name, the other one Family name.
Note: there are also some names like Furio Alipo Novanta, so in that case I want excel to keep Furio in first cell, and move other stuff next to it.
View 3 Replies
View Related
Mar 17, 2008
I have a column that is filled with text of varying lengths and I'd like to search through each cell in that column looking for a specific word or words. Unfortunately the length of the text varys greatly between each cell and there is very little uniformity so I need to be able to search through the entire entry in each cell and then highlight that row if a specific word or words are found.
I'd also like to be able to add a number "1" in another column on the same row if the search finds a word or words. Any help would be greatly appreciated.
If the cell only contains the exact word or string I'm looking for then it's easy but I can't figure out how to search through text in a cell that contains more than I'm looking for.
example: Lets say I'm loooking for "caught fire"
column Q contains:
1 "The computer caught fire after several hours"
2 "A house on the hill caught fire"
If "caught fire" exists in the cell being checked, then highlight the row and put a 1 in a specific column, lets say J.
View 14 Replies
View Related
May 30, 2012
Very simple program I think, can either be solved by build-in functions or macro. So situation is I have a table, where the D column, contains certain words.
Now I have table where the M1:M10 column contains the same words and the corresponding column (N) contains the value.
So for example , cell D5= "A" and I find that cell M6 is also "A" so I then go at cell N6 which has the value "3.3". So now I want I5 to have the value 3.3 in it.
In Summary, I want the value of the n column copied into column I. Now I have plenty of rows in the D column so I prefer a fast way.
View 1 Replies
View Related
Jun 17, 2013
I have a column filled dates in the format 01 January 2013 and I want a formula to count all the cells containing "January" for example.
View 3 Replies
View Related
Feb 6, 2007
I have some data that is imported, it's about 25,000-30,000 characters and 2,300- 3,500 words, it all comes in as one cell, A1, if I do a text to column on it I will loose everything past column IV. Is there anyway a macro can take the data and put each word in a cell by its self starting in A2? The words a have a space between them.
View 9 Replies
View Related
Jan 8, 2009
May i know how to delete the words which is red color in cell. What is the code like
View 9 Replies
View Related
Jan 14, 2009
I need a a formula that will count the number of times a word or phrase appears in a column of cells and assign a number
For example....
Big Red Ford Truck = 1
Ford Truck = 3
Red Ford Truck = 2
After i get the formula, I can sort by "greater than 2"
View 9 Replies
View Related
Oct 15, 2008
DATA VALIDATION IN MY CODE
Below is my code which looks at Table1 and converts the cell to the appropriate colour when the cell equals the Case
What i would like to do is also have the Case be Data Validation anything else error
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rRow As Range
Dim icolor As Integer
Dim ifont As Integer
With Application
.CellDragAndDrop = False
.CutCopyMode = False
End With
View 9 Replies
View Related