Find a text

Hi all
I am trying to find a text and delate a Row if the text is in the row. 
But I am not sure how to do it. 
Can someone help me?

Option Explicit
Sub FindText()
Dim  Cell As Range
For Each  Cell In ActiveSheet.UsedRange
If Cell = "Samtals hreyfing:" Then
‚the text Samtals hreyfing sin in  in the column E:E
‚If the text Samtals hreyfing: is in the row then I want to delete the Row 
End If
Next Cell
End Sub

0
Utf
2/25/2010 6:06:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
845 Views

Similar Articles

[PageSpeed] 51

I think you may be looking for this...

Sub FindText()
  Dim Cell As Range
  For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E"))
    If InStr(1, Cell.Value, "Samtals hreyfing:", vbTextCompare) Then
      Cell.EntireRow.Delete
    End If
  Next Cell
End Sub

-- 
Rick (MVP - Excel)


"Geir" <Geir@discussions.microsoft.com> wrote in message 
news:C14C7461-39B9-48F7-AAC9-A6FF714A4043@microsoft.com...
> Hi all
> I am trying to find a text and delate a Row if the text is in the row.
> But I am not sure how to do it.
> Can someone help me?
>
> Option Explicit
> Sub FindText()
> Dim  Cell As Range
> For Each  Cell In ActiveSheet.UsedRange
> If Cell = "Samtals hreyfing:" Then
> ‚the text Samtals hreyfing sin in  in the column E:E
> ‚If the text Samtals hreyfing: is in the row then I want to delete the Row
> End If
> Next Cell
> End Sub
> 

0
Rick
2/25/2010 6:27:08 PM
You want to delete all the rows that have that string in it:

Option Explicit
Sub testme()

    Dim FoundCell As Range
    Dim wks As Worksheet

    Set wks = Worksheets("sheet1") 'change this!

    With wks.Range("E:e")
      Do
        Set FoundCell = .Cells.Find(what:="Samtals hreyfing:", _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                           
        If FoundCell Is Nothing Then
            'no more left, get out of the loop
            Exit Do
        Else
            FoundCell.EntireRow.Delete
        End If
     Loop
   End With
End Sub

Using Edit|Find is usually quicker than looping through all the cells.


Geir wrote:
> 
> Hi all
> I am trying to find a text and delate a Row if the text is in the row.
> But I am not sure how to do it.
> Can someone help me?
> 
> Option Explicit
> Sub FindText()
> Dim  Cell As Range
> For Each  Cell In ActiveSheet.UsedRange
> If Cell = "Samtals hreyfing:" Then
> ‚the text Samtals hreyfing sin in  in the column E:E
> ‚If the text Samtals hreyfing: is in the row then I want to delete the Row
> End If
> Next Cell
> End Sub

-- 

Dave Peterson
0
Dave
2/25/2010 6:29:15 PM
This may miss consecutive rows with that value in it.  If I were looping through
the range, I'd either start at the bottom and work my way toward the top.

Or build a range to be deleted and delete it after all cells are compared.



Rick Rothstein wrote:
> 
> I think you may be looking for this...
> 
> Sub FindText()
>   Dim Cell As Range
>   For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E"))
>     If InStr(1, Cell.Value, "Samtals hreyfing:", vbTextCompare) Then
>       Cell.EntireRow.Delete
>     End If
>   Next Cell
> End Sub
> 
> --
> Rick (MVP - Excel)
> 
> "Geir" <Geir@discussions.microsoft.com> wrote in message
> news:C14C7461-39B9-48F7-AAC9-A6FF714A4043@microsoft.com...
> > Hi all
> > I am trying to find a text and delate a Row if the text is in the row.
> > But I am not sure how to do it.
> > Can someone help me?
> >
> > Option Explicit
> > Sub FindText()
> > Dim  Cell As Range
> > For Each  Cell In ActiveSheet.UsedRange
> > If Cell = "Samtals hreyfing:" Then
> > ‚the text Samtals hreyfing sin in  in the column E:E
> > ‚If the text Samtals hreyfing: is in the row then I want to delete the Row
> > End If
> > Next Cell
> > End Sub
> >

-- 

Dave Peterson
0
Dave
2/25/2010 6:32:32 PM
To Dave: Aw crap! Of course you are right... I just went too fast in trying 
to modify what the OP posted. Thanks for catching this!

To Geir: Use Dave's code... it will be faster than any loop that looks at 
all individual cells in the range.

-- 
Rick (MVP - Excel)


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4B86C240.B7E76635@verizonXSPAM.net...
> This may miss consecutive rows with that value in it.  If I were looping 
> through
> the range, I'd either start at the bottom and work my way toward the top.
>
> Or build a range to be deleted and delete it after all cells are compared.
>
>
>
> Rick Rothstein wrote:
>>
>> I think you may be looking for this...
>>
>> Sub FindText()
>>   Dim Cell As Range
>>   For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("E"))
>>     If InStr(1, Cell.Value, "Samtals hreyfing:", vbTextCompare) Then
>>       Cell.EntireRow.Delete
>>     End If
>>   Next Cell
>> End Sub
>>
>> --
>> Rick (MVP - Excel)
>>
>> "Geir" <Geir@discussions.microsoft.com> wrote in message
>> news:C14C7461-39B9-48F7-AAC9-A6FF714A4043@microsoft.com...
>> > Hi all
>> > I am trying to find a text and delate a Row if the text is in the row.
>> > But I am not sure how to do it.
>> > Can someone help me?
>> >
>> > Option Explicit
>> > Sub FindText()
>> > Dim  Cell As Range
>> > For Each  Cell In ActiveSheet.UsedRange
>> > If Cell = "Samtals hreyfing:" Then
>> > �?sthe text Samtals hreyfing sin in  in the column E:E
>> > �?sIf the text Samtals hreyfing: is in the row then I want to delete 
>> > the Row
>> > End If
>> > Next Cell
>> > End Sub
>> >
>
> -- 
>
> Dave Peterson 

0
Rick
2/25/2010 6:42:55 PM
Reply:

Similar Artilces:

Find feature does not work with HTML messages (Content-type: text/html)
I have Outlook 2000 SP-3 running under Windows ME. When using the Find feature to locate all e-mails with a given word in the message, no items are found if the e-mails are of "text/html" content-type. The Find feature appears to work for any messages that have URLs embedded and are "multipart/alternative" or "text/plain" content-type. I noticed a similar post several months ago (see http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=%23qSBYIssDHA.2224%40TK2MSFTNGP09.phx.gbl). Is there a work-around for this problem, or should this be reported t...

Summing instances of text across worksheets
Hello, I have a workbook with separate sheets for each month. The sheets contain a list client names (entered exactly the same on each sheet). I need to summarize how many times a client name appears in total. For example, John Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec (=3). I've been trying pivot tables without luck. Hoping someone can help. Thanks in advance! Never mind, I consolidated all the worksheet data into one and the pivot table works fine. Thanks anwyay! "George" wrote: > Hello, I have a workbook with separate sheets fo...

Excel should default to autoformat for chart text boxes
Excel in (Office 2003 and as far as I can tell all previous versions of Excel) should default to autoformat for chart text boxes - it currently selects "none" for outline and background options ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

Need help extracting text from EDLs
I really am trying to learn this stuff, but I haven't programmed anything since my TRS-80 Basic days and I'm a bit overwhelmed. I want to extract text from Edit Decision Lists so that I have a list of shots used in a film. Here is an example of one event of an EDL: 002 TAPE004 V C 04:45:22:06 04:45:24:14 02:00:08:00 02:00:10:08 FROM CLIP NAME: 7C-4_B_ DLEDL: PATH: /raids/luc_1/bun/reel_02/oscans/1222/bun_7c_4_b_02/2048x1556/ The text that follows "FROM CLIP NAME:" is the name of the clip that I want to extract so that I have a document on onl...

How the heck do I find tables, views, forms etc... in Access 2007
Can Access 2007 navigation be any more convoluted? I've been spending close to an hour trying to find a single table, query form, etc... now that the navigation has been changed. Is there a way to get the old style back? It does take some time - more than an hour :-) - to become familiar with the new interface in A2007 (NavPane, ribbon, ...) Suggestions: - The title bar at the top of the Nav Pane includes a tick box for: All Access Objects - Set Category to: Object Type - The categories (Tables, Queries, ...) collapse. - Show and use the Search Bar. It filters objects as...

Automatically display set text based on users composition
Hi, im trying to do something really simple, trouble is i dont know what the feature's called to be able to search for tips on how to do it. Basically in outlook messages, when a user begins writing a sentence e.g. "in the terms of" i need a tag to pop up that allows the user to press enter and then the remainder of what they will want to type in will be inserted in, its a yellow tag that comes up above the words. i dont know where it needs to be created and enabled. Cheers, Rhys. ...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

Code to send SMS text message?
Hi, I would like to send an SMS text message to a cel phone via code within Excel 2007. Has anyone done this? What code / services / products do I need to accomplish this? Does anyone have an example? Thanks in advance. --Dan What gateway are you using? What you would need to do is to snd a internet message to a gateway provider which would then send the messae out on the wireless land. Most likely you are using a webpage interface from your PC to interface with the gateway. So opening up an internet explorer and goiong to an URL is probably the method to use. ...

Access 2007 Rich Text Question 04-28-07
Hi All In the plan text version of a Memo field you can easily add a date by pressing Ctrl+Colon which makes life easy for the end user. However, if the Memo field is set to the Rich Text Property this no longer works. Or am I missing something? Tom gave me a part answer last week but I cannot see a way of inserting a date on a Rich Text Memo field by using keystrokes Thanks ...

Find touched files since a specific timestamp
Hi, Given the name of a folder, what's the best way to find the list of files (include files in sub-directories) that were changed since a specific timestamp? I tried to add the list of files to a CStringArray and then sort them. But it took a while to process some 1000 files. There must be a better way. Please advise. Best, Isaac http://www.globalrendering.com Which sorting method have you used? qsort does 1000 sorting in several ms. Anyway, if you want to get files in a date range, just check date before adding to array, use CFileFind. -- Igor Green, Grig Software. www.gri...

Text box and formatting?
I am working on class schedule and have one text box which spans a few pages. Pages are double columns. I have a header for each class and paragraph given for class description. On the last page, last column, one description appears at the top with empty space for the rest of the column. I would like to change the formatting of the text in the whole text-box so that most of the last column is used. I can select the text in the text box which expands over the few pages and play with the font size, but that would mean that all the text would be the same size. Is there a way that text...

How do I use text in a cell as a range name in a formula
If cell A1 had the text TEST in it and TEST is the name I have given to a group of cells using the name box what formula can I use to give me the sum of TEST, thats is the sum of the cells in the group called TEST. I understand that I can simply have =SUM(TEST), but I want the formula to refer to Cell A1 to get the name ie =SUM(A1) doesn't work obviously Any help appreciated Thank you In this case, you want to use the INDIRECT function. E.g., =SUM(INDIRECT(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kiwi" &...

Finding Attachments
Does Outlook 2002 Advanced Find provide a method for locating an attachment in an e-mail??? regards, Adrian Yes, you can search for all items with attachments, but you cannot search for a) specific attachments or b) attachment contents. To perform this type of search, simply right click on any Outlook folder, choose Advanced Find. In the Advance Find window, select Other Choices, then select "Only items with: one or more attachments". -- Hope that helps!! Thank you! Sincerely, Chris Lineback, MCSE Microsoft Enterprise Messaging Support - Please do not send email directly...

How do I find duplicate rows in a list in Excel, and not delete it
I have a long list of data in Excel that is 3 columns wide. I need to find and save only the duplicate rows but don't want to delete them. Instead, I could delete the unique rows and keep the duplicate rows. In the customer assistance, I found out how to delete duplicate rows and save the unique rows, but this is exactly opposite of what I want to do. This is one option: =COUNTIF($A$4:$A$18,A4)>1 copy down, and use a Autofilter to find all True HTH Ola Sandstr�m Picture encl.: http://www.excelforum.com/attachment.php?attachmentid=3498&stc=1 +---------------------------...

wrapping text in a query field
I set up the field in table to memo and tried entering a lot of information but when I open the report that field does not expand to show all of the entries. Can this be done in query and reports both. Thanking you in advance. Mary Lou On Dec 12, 12:06 pm, MaryLou <Mary...@discussions.microsoft.com> wrote: > I set up the field in table to memo and tried entering a lot of information > but when I open the report that field does not expand to show all of the > entries. Can this be done in query and reports both. > > Thanking you in advance. > Mary Lou Go to the p...

Find Blank Cells
I wish to replace the blank cells in a large database with a zero (0). I cannot figure out how to find a blank cell using the Find and Replace option under the Edit menu. Can anyone show me the way? Hi Peter, I always use CTRL+H to bring up the find and replace menu, leave the fine part empty, and put in what you want to replace it with....however, I do notice you say in a "database"? Do you mean in Access?? Dave M. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...

Finding unique numbers in a column
Is there any way to find the number of unique values among a set o values in a column in an excel sheet. I would also like to know th number of times each value appears in the column. Thank -- coolkid39 ----------------------------------------------------------------------- coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431 View this thread: http://www.excelforum.com/showthread.php?threadid=37924 There was also a similar thread under Excel Worksheet functions titled "Delete duplicates". Solution provided was to use: Data>Filter&g...

Pasting "Character Spacing" adjusted text
Our church office secretary has the Bible on a CD in a Word document. They have used Format>Font>Character Spacing>Position >Raised>by 2.5 rather than "Suberscript" for all the verse numbers. Is there a way to Copy the text and paste it into Publisher to maintain the appearance of the raised verse numbers? I do not see the Character Spacing formatting features in Publisher. We are using Office 2002. Jim wrote: > Our church office secretary has the Bible on a CD in a Word document. > They have used Format>Font>Character Spacing>Position >Raised>...

Publisher does not recognize tab-delimited text files in a mail m.
I am a student who is learning MS Publisher 2003. In mail merges, publisher does not recognize tab-delimited text files. If you try to mail merge with a tdt file, the data appears as one column entry, making it impossible to sort by separate fields or insert separte fields as a formally formatted address block. Converting the file to a comma-delimited text file (comma separated values) solves the problem. The fields appear separately as they should in the Mail Merge Recipients dialog box, so that you can sort by separate fields and then set up a properly formatted address block. Pl...

i cant find my email documents
After installing an antivirus to my xp computer i cannot find all my past email. it gives a socket error number 10060 and an error number of 0x800CCC0E Please help "bAyai" <anonymous@discussions.microsoft.com> wrote in message news:b3e101c4373f$9fc2fd40$a501280a@phx.gbl... > After installing an antivirus to my xp computer i cannot > find all my past email. it gives a socket error number > 10060 and an error number of 0x800CCC0E > Please help If you uninstall it, does the error go away? ...

deleting lines on text boxes
I do not want to print the lines in a text box in microsoft publisher. How do I do this? If you mean the line *around* a text box:: Click inside the text box Right-click - select Format Text Box Colors and Lines Tab - under Lines|Color, make sure it says No Line Click OK -- JoAnn Paules MVP Microsoft [Publisher] "Paul Rozewicz" <Paul Rozewicz@discussions.microsoft.com> wrote in message news:C89EDDA3-8F82-4F19-BA70-75FE82061B93@microsoft.com... >I do not want to print the lines in a text box in microsoft publisher. How > do I do this? ...

How can I write in a text in a cell using numbers and the letters.
I'm trying to get some codes in for example 511E09 is one fo them but when I try to enter another code it makes the code a formula and it changes to 5.11E+09 why is that? I tried changing it by formatting cells to text, but that doesn't change it. Waht can I do? Clear the data, change it to text, and then enter it. -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:9C474C50-E47D-43EB-A4F0-B4D21141E39F@microsoft.com... > I'm trying to get some codes in for example 511E09 i...

find first number
How do I find the first number in a string? I need to update a field by removing anything that could not be a number. Unfortunately, there's no specific number of characters which I can use to start and stop. Intentions are to replace everything before the first number and after the last number. Fortunately, the numbers I want are grouped together, but in the missle of strings. thanks Here is one example: CREATE TABLE Foo ( foo_key INT PRIMARY KEY, foo_data VARCHAR(35)); INSERT INTO Foo VALUES (1, '1'); INSERT INTO Foo VALUES (2, 'aa21a'); ...

Formatting a text box for date entry only
Is there a way to format a text box on a user form to only allow date entries ie: mm/dd/yy or mm/dd/yyyy? Not really, but you can ensure it is a date, like so Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim fValid As Boolean fValid = False On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If IsNumeric(.Value) Then If IsDate(.Value) Then If Year(.Value) >= 1970 And Year(.Value) <= 2999 Then ...

Change text name based on file name
On my spreadsheet in cell A1 I have a text label containing the word "Department" What I want is when I save the file as Schedule 1 (Division).xls I want cell A1 change from "Department" to "Division" based upon what is within the parentheses in this example Any tips will be appreciated. Thank you. If I understand you, you want to return the portion of the wb's name that is within parentheses. If so, try this formula in cell A1. After doing a "Save As..." you will have to press F9 to update. Minimal testing: =MID(CELL("filename&quo...