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