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

Clearing Rows Containing Specified Words

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

Clearing Unprotected Cells

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

Clearing Contents Of Cells

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

How To Stop From Clearing Hidden Cells

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

Clearing Merged, Multiple Cells

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

Clearing Selected Cells On A Form

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

Clearing Cells Based On Condition

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

Clearing Contents Of Specific Cells

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

Clearing Unlocked Cells On A Protected Worksheet

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

Deleting / Clearing Multiple Cells In Target Range

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

Run A Macro Called "PriceForm" Instead Of Clearing The Cells

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

Clearing Of The Cells Clears As Well The Copying Of "B30"

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

Sum Up Cells Containing Certain Words

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

Replace All The Times That These Words Appear In The Rest Of The Sheet With The Words In Column B

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

Show Cells That Contain 2 Words

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

Combining Words In Cells

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

Seperating Words To Different Cells

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

Extract Certain Words From Cells

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

Macro To Only Keep 100 Words In Range Of Single Words Per Column?

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

Auto-calculate 2 Column That Contains Numbers And Words Words

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

Separating Words In One Cell Into Many Cells

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

Autosplit Two Words From One Cell Into Two Cells

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

Searching Text In Cells For Words

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

Find Corresponding Cells - Column Contains Certain Words

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

Formula For Counting Cells Containing Certain Words?

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

Split Words In One Cell To Many Cells

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

Delete Words With Certain Colour In Cells

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

Formula To Count Words In Cells

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

Restrict Cells To List Of Words

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







Copyrights 2005-15 www.BigResource.com, All rights reserved