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


ADVERTISEMENT

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 View Related

Clearing Specified Rows After Defined Row?

Feb 20, 2014

I'm looking to clear the contents of the 2 rows after my previously defined last row. So for example, if my last row is defined as LastRow1, I'd like to clear the contents of thr 2 rows below LastRow1.

View 2 Replies View Related

Separate Whole Words Into Two Rows

Dec 29, 2005

I want to separate the following words into two rows. As the length of each word is different, so i could not use 'text to coumns' function.
for example:
Word1 : the board of directors of abc company limited (len = 45) in cell u13
Word2 : the board of directors of abcdefgh company limited (len =50) in cell u14

I use the formula to separate word 1 into two rows: return #VALUE
1st row : =IF(LEN(U13)<40,U13,LEFT(U13,FIND(" ",U13,40))) in cell u15
2nd row : =IF(LEN(U15)<40," ",TRIM(MID(U13,LEN(U15),99))) in cell u16

But i use same formula to separate word2 into two rows : successful
1st row : =IF(LEN(U14)<40,U15,LEFT(U14,FIND(" ",U14,40))) in cell u17
2nd row : ==IF(LEN(U14)<40," ",TRIM(MID(U14,LEN(U17),99))) in cell u18

Answer : 1st row : the board of directors of abcdefgh company
2nd row : limited

View 12 Replies View Related

Delete Rows That Contain Specific Words

Nov 10, 2006

how to create one macro: I have a spreadsheet with 8 columns with various text headings. Each column has different data (some show dates, some show only figures, some show text). Specifically, column E (entitled "Sub Item Reference") has various text entries (e.g. "Newsletter" or "Booklet"). I am hoping to find a simple macro that will search column E and delete any rows where that column contains the text "Cover" or "Label". I'm sure it's the simplest thing going but I can't seem to work it out.

View 7 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

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

Find Words - Deleting The Words - And Deleting Some Info After The Words.

Jun 23, 2006

I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.

I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.

View 3 Replies View Related

How To Turn Abbreviated Words Into Full Words

Feb 4, 2013

On to the topic, I have all the US and Canadian states abbreviated (CO = Colorado, etc.) and was wondering if there is a way to make all of them convert to their respective names in one fell swoop instead of writing it in for each one, one at a time. Something along the lines where I can make, CO = Colorado, TX = Texas and then hit enter and all of the abbreviations would convert. Some kind of command.

View 2 Replies View Related

Sort On Number Of Words (words With Least Characters First)

Jan 22, 2010

I want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.

View 9 Replies View Related

Count Words In Range Mixed In With Other Words

Nov 9, 2006

I have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".

View 7 Replies View Related

Combining List Of Words Into New Words, Conjugation

Feb 15, 2007

I have 4-5 separate lists of words/letters and I would like to combine them in a certain order but creating all possible combinations resulting a new word. It's like a conjugation tool for verbs. For example if List 1 = be/el/ki/meg; List 2= m List3=e/é; List4= gy/" "; List 5=" "/ek/sz/nnek So the output has to be (if the order is List1+List2+List3+List4+List5)= bemegy/bemegyek/bemegysz/bemesz/bemész/elmegy/elmennek/ ..... and all possible combinations.

Preferentially I would like to automate it in a way that it straight outputs into MS Access. Also can it be automated that it reads the list from a file (database)?
(Also can it recognize the word by letters? Because the basic word that I would load the program= List2+List3+List4)

View 2 Replies View Related

Clipboard Clearing

Jul 3, 2009

I am running into trouble with too much data accumulating on the clipboard in XL2007, so is there any way to set cutcopymode=false (or some other function) so that the clipboard is automatically cleared after every paste event throuout a macro project without having to restate cutcopymode=false after every paste?

View 7 Replies View Related

Clearing Text

Jun 15, 2007

I have a spreadsheet with values and text in Column G. I would like VBA code that will clear only clear the text in column G drom row 9 onwards.

View 9 Replies View Related

Clearing A UserForm

Aug 15, 2007

I have a UserForm called Menu. The first line of code behind the OK button says

Menu.Hide

When I run this using F8, it behaves itself and the menu disappears after the code line Menu.Hide. When I click the OK button to run it normally the menu stays on the screen partially hidden behind the next sub menu.

View 9 Replies View Related

Listbox Clearing

Dec 3, 2009

I have 4 listboxs on a Userform populated by rowsource

I have code that will not allow conflicting selections of the list boxs

EG listbox1 conflicts with listbox3
listbox2 conflicts with listbox4

What I am trying to do is when the user clicks on listbox1 and listbox3

I clear the selections from 1 and 3 leaving no highlted blue in the boxs

My code runs it clears the first listbox and not the second

I have tried using boolean to clear them but it is the same thing

here is my code below

If shOptions.Cells(r, c) = "" Then
MsgBox "You have selected conflicting Alterations"
UserForm1.Controls(h.Name).ListIndex = -1 *this works
UserForm1.Controls(lb.Name).ListIndex = -1 * this does not fire
Exit Sub
End If

If I change the code to
UserForm1.Controls(lb.Name).ListIndex = -1 * this works
UserForm1.Controls(h.Name).ListIndex = -1 *this does not

View 9 Replies View Related

Clearing Arrays

Apr 9, 2005

I am inputting strings into an array and want to clear (i.e remove the contents of all the array) after i have finished witht the array.

View 9 Replies View Related

Clearing Checkboxes With Vba

Oct 25, 2006

I wish to create a macro that clears data and uncheck all checkboxes (forms). I have attached my excel file.

View 2 Replies View Related

Clearing Form Controls?

Nov 18, 2009

I have two linked ComboBoxes on a form. There are 10 Textboxes populated by ComboBox2’s choice. These controls are situated on two frame controls which are used for visual groupings only. I would like to clear everything except Combo1 when Combo1’s value is changed. ( Combo1 is a filtered key list using the dictionary. Script code)

I’ve written a small sub that is called to clear the textboxes, but its not reliable every time. It seems that if the scroll bar is used, and the user chooses Combo1 choice “NUTS/SEEDS”, the boxes aren’t cleared. In fact it will show the “Walnuts” info in Combo2.

View 4 Replies View Related

Stop VB From Clearing Clipboard

Apr 6, 2010

I have a file with Workbook Activate/Deactivate and Worksheet Activate/Deactivate commands in it. When someone is trying to copy information from or to this workbook the VB automatically clears the clipboard (or at least that's how I understand it), which makes it so they can't paste that data anywhere else. How can I prevent this, but still keep my current commands?

View 9 Replies View Related

VBA / Clearing Text In Code

Feb 22, 2013

I have this code in the Worksheet

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1).Value = "FINAL" Then Call SHOW_7
End Sub

I have this code in a module

Code:
Sub SHOW_7()
' SHOW_7 Macro
With Worksheets("Sheet7")
.Visible = xlSheetVisible
.Select
Application.Wait Now + #12:00:05 AM#

[code].....

I need the word FINAL cleared off after the sheet is very hidden at the end

View 5 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

Paste Buffer Clearing

Jun 17, 2008

I wrote a little program to highlight the cell that the cursor is currently selecting. The code is in Workbook_SheetSelectionChange. Everything works great, except that the paste buffer now clears itself so I cannot use paste/cut.

View 9 Replies View Related

LISTBOX: Counting And Clearing

May 15, 2003

regarding listboxes within user forms.

1) I need to capture the amount of selections (regardless of what they are) that were made by the user within the listbox. How can I do this?

2) I want the user to be able to use the form continuously, there I have created a command button to clear the options. I am able to clear the options for the rest of the inputs but not the listbox.

View 9 Replies View Related

Clearing Large Arrays

May 24, 2006

I am looping through a bunch of code, and each time I loop I would like to clear very large arrays.

View 3 Replies View Related

Clearing Check Boxes

Sep 23, 2006

I have 50 check boxes not on a user form, but on spreadsheet. I attached a button that says "Clear All" (meaning 'clear all checkboxes'). I can write a sub smth like

Private Sub CommandButton1_Click()
CheckBox1.Value = False
CheckBox2.Value = False
End Sub

... and so on for all 50 check boxes. Any simpler way to do this in a loop? Smth like

For i=1 To 50
Checkbox............ .Value=False
Next i

I just cannot figure out the syntax.

View 9 Replies View Related

Clearing Content Of All Sheets

Oct 12, 2006

I wrote macros that clear content of all sheets, but does'nt work correctly

Sub ClearContent()
Dim wsSheet As Worksheet
On Error Resume Next
For Each wsSheet In Worksheets
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Next wsSheet
On Error Goto 0
End Sub

View 2 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

Clearing The Clipboard In Code

Jul 17, 2007

My workbook has much copy and past from one sheet to another after a web query, eventually it freezes.

I suspected all along it had something to do with the Copy/Paste and the clipboard required to be absoultely emptied.
The code I used to "clear the clipboard" is:

Declare Function CloseClipboard Lib "user32" () As Long
Declare Function EmptyClipboard Lib "user32" () As Long
Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long

Sub ClearClipboard()
OpenClipboard 0&
EmptyClipboard
CloseClipboard
End Sub

But when I checked out some freeware in relation to clipboard viewers etc, it seems after running this code it does not absolutely empty the clipboard.

How can I empty the clipboard entirely or am I not constructing my work correctly ?

View 9 Replies View Related







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