search data

I need to seach a cell range (C2: C last cell) for a specific string. Once 
found i need to delete the entire row and continue searching. Can someone 
help me with this? Thanks.
0
Utf
2/9/2010 5:28:01 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
502 Views

Similar Articles

[PageSpeed] 45

search help for find next.

-- 


Gary Keramidas
Excel 2003


"Daniel M" <DanielM@discussions.microsoft.com> wrote in message 
news:9B86D78C-9BEB-4BFC-9181-0F0C082866A1@microsoft.com...
>I need to seach a cell range (C2: C last cell) for a specific string. Once
> found i need to delete the entire row and continue searching. Can someone
> help me with this? Thanks. 

0
Gary
2/9/2010 5:40:55 AM
hi, Daniel !

> I need to seach a cell range (C2: C last cell) for a specific string.
> Once found i need to delete the entire row and continue searching.
> Can someone help me with this? Thanks.

regarding this "specific" string...
- is (upp/low)er case important ?
- is it a start-string, middle-string, end-string ?
- how many do you expect to be found ? (1, 10, 100, 1000, ...)

(just trying to be in "specific" help to you) :D

regards,
hector. 


0
H
2/9/2010 5:46:32 AM
Hi Daniel

Try out the below

Sub Macro()
Dim varFound As Range, varSearch As String

varSearch = "jac"

With Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
Set varFound = .Find(varSearch, LookIn:=xlValues, SearchDirection:=xlPrevious)
Do While Not varFound Is Nothing
varFound.EntireRow.Delete
Set varFound = .Find(varSearch, LookIn:=xlValues, SearchDirection:=xlPrevious)
Loop
End With
End Sub

Check out help on Range.Find and adjust the code 
LookAt    Optional Variant. Can be one of the following XlLookAt constants: 
xlWhole or xlPart

-- 
Jacob


"Daniel M" wrote:

> I need to seach a cell range (C2: C last cell) for a specific string. Once 
> found i need to delete the entire row and continue searching. Can someone 
> help me with this? Thanks.
0
Utf
2/9/2010 6:51:01 AM
This worked perfectly. I was looking at the .find help but was having 
problems with the syntext. Thank you!

"Jacob Skaria" wrote:

> Hi Daniel
> 
> Try out the below
> 
> Sub Macro()
> Dim varFound As Range, varSearch As String
> 
> varSearch = "jac"
> 
> With Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
> Set varFound = .Find(varSearch, LookIn:=xlValues, SearchDirection:=xlPrevious)
> Do While Not varFound Is Nothing
> varFound.EntireRow.Delete
> Set varFound = .Find(varSearch, LookIn:=xlValues, SearchDirection:=xlPrevious)
> Loop
> End With
> End Sub
> 
> Check out help on Range.Find and adjust the code 
> LookAt    Optional Variant. Can be one of the following XlLookAt constants: 
> xlWhole or xlPart
> 
> -- 
> Jacob
> 
> 
> "Daniel M" wrote:
> 
> > I need to seach a cell range (C2: C last cell) for a specific string. Once 
> > found i need to delete the entire row and continue searching. Can someone 
> > help me with this? Thanks.
0
Utf
2/10/2010 5:19:01 AM
Reply:

Similar Artilces:

How do I create a mailing list with current data in Excel?
I have a list of names, addresses and I would like to make it a list that can be used in a mail merge. Is this possible and how? http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- Regards, Peo Sjoblom (No private emails please) "Amanda" <Amanda@discussions.microsoft.com> wrote in message news:095EF751-B349-4C6C-BA5B-9D91ADF7DA9E@microsoft.com... >I have a list of names, addresses and I would like to make it a list that >can > be used in a mail merge. Is this possible and how? ...

Utilizing a portion of data in SUMPRODUCT
Based on prior input from the group, I am using the following formula: =SUMPRODUCT(--(Log!B3:B80=1997),--(Log!E3:E80=17)) The data in Column B is actually a full date, entered yyyy/mm/dd. With the data in this format, I get a formula result of 0. If I eliminate the /mm/dd, I get the correct result of 1. I have tried the formula with and without quotes around the Condition. The person for whom I am creating this spreadsheet has asked that the date be kept together, rather than separating out the year. Is there some way I can maintain the data in Column B and change the formula to ge...

Collecting data
I have an evaluation sheet created in Excel 2003. I have a number of questions which have responses eg. poor, fair, good, excellent etc. These will be answered by ticking in an ‘ActiveX Check Box’. Beside each question the respondent can add comments, for this I used the ‘ActiveX Text Box’ with a scroll bar. We usually only send this form out to random attendees and getting about 5-10 responses. It was not difficult to collect the data for such a small number. What has happened now is that a colleague has sent the form out to everybody that attended one of our presentations and ...

Data Organized
Dear All, I have list of names (column A) and every time I put a code (example: 2), instantly the function will create a list in another column. For example Data: A B NAME CODE AA BB 2 CC DD 2 EE Result: BB DD And if I put another code (3 for example), the function will create another list at next column. Please help. Best Regards, Gusur Try this array formula =IF(ISERROR(SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))),"", INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1)))) copy down and across as required -- __________________________...

Transfer data from sheet to sheet
I'm currently working on a quotation and I have 3 seperate worksheets I'm working with. Sheet 1 has all the model name, elevation, part description, quantity, cost per piece and total. This worksheet is for internal use only. Sheet 2 has all the products and prices listed. (Probably has no purpose for my question) Now Sheet 3 is where I am trying to pull only certain data from Sheet 1. What I am trying to do is transfer only the columns labeled "model name", "elevation" and "total" Obviously the easy way to do it is to just cut and paste but since...

hiding control textbox when no data
Hi, I am wondering if there is a way to hide the whole textbox control and does not leave big space when it has no data/value in the Access report. Any help would be greatly appreciated. Thank you in advance Define "big space". Do you mean horizontally or vertically? Have you set the text box Can Shrink property to Yes? -- Duane Hookom MS Access MVP "Associates" <Associates@discussions.microsoft.com> wrote in message news:4964B131-5BBE-4249-8F0C-2C00ED0AA012@microsoft.com... > Hi, > > I am wondering if there is a way to hide the ...

Linking rows of data to another worksheet
Worksheet One contains survey data pertaining to customer satisfaction for all of our building communities. The data is entered in each row as follows.. .. community name, lot number, buyer name, etc. Therefore, this worksheet contains all the survey results for all of our buyers, and then based on the survey responses, an overall rating is calculated. I would like to then link each row to its corresponding worksheet per community. By doing this, I can calculate the survey ratings per community as opposed to the overall rating calculated on worksheet One. I would greatly appreciate any as...

Using formula to calculate data source range
Is there any way to referencing to data source range with formula? For example I would like to insert this formula to the data surce page (if it would be possible) : =Address(32;12;1;True;"DEF_Montly")&":"&address(32;DEF_Monthly!$A$1;1;True) its value is: "DEF_Monthly!$L$32:$Q$32" So this way I could expand or reduce the range dinamically. Can anyone help me? Edit. You can't link the whole data range of a chart to a dynamic range like this; Excel accepts the name but converts it into a non-dynamic cell address. You can link the X and Y values of...

Sorting more than 3 pieces of data??
Wow, I've _never_ had this happen before. I have a spreadsheet that has 4 columns, 3 that are often extremely similar. It's the 4th one that has the most variation in the data, and it's not getting sorted. I must have then sort by the four, unfortunately. As only 3 fields come up in the data sort option, was wondering if anything can be done? Thanks. First sort all data only by that column that you'd indicate as the last (4th) field in the sort by tool. Then sort all the data again by the first 3 columns. Hi Sub-sorts stick. So 1) sort by criteria 3, 4 2) sort by c...

Taking all BU data offline
Hi I am trying to find a way that a user can take all account - contact data offline in the outlook client relative to the Business Unit that they belong to. I have looked through all the options in the Local Data filters but am so far unable to find a way to do this as the Business unit field is not available in the options. Has anyone managed to do this ? Cheers Brian Here you go: Under the new Data Group: Account >> Select "Related Owner" >> Business Unit Do the same for contact and other objects you want to bring down Frank Lee Workopia, Inc. http://www.w...

Excel 2007: Conditional Formatting and Ribbon Data
I have several cells have conditional formatting equations which refer to a date value in cell H1 (for example). I want to be able to move the value stored on this cell to a control on a Ribbon. 1) Is it possible to put a Date Picker control on a Ribbon? 2) Is it possible to refer to the value of this or any other control on a Ribbon from a Conditional Formatting equation? Thanks! JJ ...

back up data to outlook
Hello I have a friend that did not back up his outlook data from outlook in the export fasion. He made a back up of the documents and setting folder and dragged it to a cd. Is there any way to restore the outlook data to a new install of outlook???? Thank you again in advance. Yes, search for pst-files and make sure you include hidden files when searching. In fact copying the pst-file is a better backup than exporting the pst-file. Also see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Na...

Importing private calendar data to a public calendar
Hi, I have exported a private calendar to a PST file and I'm trying to import it into a public calendar I have created. I get a message saying "You have chosen to import a folder whose type differs from that of the selected folder. The items will be imported into a new folder with the same name." However it doesn't import it to anything I can find. I am able to import the data into another private calendar though so I no the data is good. Any suggestions on how to transfer private calendar data to a public folder calendar ? Thanks, Tony Tony Tuccillo wrote: > H...

Searching multiple excel documents
When searching for P.O. numbers in windows 2000 basic search command I enter the *.xls in the files or folders field, and i enter the P.O. number in the Containing text field but no xls documents show up. i've selected to search through all subdirectories and am positive the P.O. number is in a xls document, as i've cut and pasted it to test this. I've tried doing alpha numeric searches that are withing xls documents and this returns nothing aswell. doing a pure text search in all documents does return positive, so my search funtion does work for containting text. does wind...

office 07: auto powerpoint charts using named range data in excel
i am trying to create code that goes down a list of named excel ranges and creates a complete ppt chart (title with graph and annotated text), one for each excel range. the chart will be customized based on the contents of the named excel range. there may be easier ways, but i thought that if i could reference and change the ppt chart data directly from the excel file procedure, i can set it equal to the excel file data range name that i want. i have been trying to use sourcedata and chartdata but i keep getting compiler errors. any help is welcome, even if it is a reference book that...

Problem with Filtering Form with Subform in Acces Data Project 200
When I aplly filter (by form) to a form with subform in Acces Data Project 2007, subform loose data; subform does'n show any data; Moveing to next/prev. record in main form doesn't synchronise subform wich is still and always blank; I must close and reopen form to get again subform data... Main form contains about 50000 records and subform contains 30-60 records related to main form selected within 1,500,000 records in table. Moveing to next/prev. record work correctly when filter is not applied. Acces Data Project 2007 use SQL Server 2005 Express SP2 installed on Windows Vista...

Data Migration Framework #2
Hello, somebody can migrate email with DMF ???... somebody can put a sample off record in DMF table's fopr email migration... My problem is that the email was migrated but the Sender and To is blank on MS CRM... so the field Regarding Off is empty en MS CRM... thanks a lot ... Marcos Paione ...

Copying data without blank cells, and only certain cells
I have 3 questions: 1). I receive large data sets from scientists in excel format that' been transferred from some instrument. In column A are numbers usuall starting from 200 to 780 counting up every one unit. In the column is the data corresponding to the numbers in column A. I'm trying t figure out a formula that will copy the number 200 and every 5 unit thereafter (200, 205, 210...etc) in one column plus the data tha corresponds to that number in other column. 2). Also, in some data sets I receive, there are an even amount o blank cells between each filled cell. So A1 w...

Data transfer
I am receiving data by a plc access program into a cell.How do I move it down a list as fresh data arrives. ...

Retrieving data with two variables
Hi, First time. Looks like you all have some great advice. This will probably be very easy for most of you. I would like to create one worksheet were I choose a certain doctor (Drop down box), the year and it matches the month, year and doctor and guves me the correct production $$ for that doctor. I have used the lookup formulas but can't seem to figure how I should write this formula. Thank you for any help. -- Many Thanks, Jennifer If you only give it the year, how will it match on the month? If you give it a date, too, then you could use something like this: I put my table o...

How can I convert data from excel to address labels?
I've been asked to convert a list of addresses that are in an excel spreadsheet into address labels. Would appreciate any help! Thanks. See Mail Merge in Microsoft Word. It has wonderful instructions "KC" <KC@discussions.microsoft.com> wrote in message news:4ECF5BD3-97C3-4978-838A-C6DA2D55FB4A@microsoft.com... : I've been asked to convert a list of addresses that are in an excel : spreadsheet into address labels. Would appreciate any help! Thanks. You may want to read some tips for mailmerge: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org...

can i short data with excel formula
if column A1 has any value and column A3 has any value and column A7 has any value , so can i short these value with excel formula..... You'll need to provide a clearer explanation of what you are looking for. A1, A3 and A7 are cells, not columns. What does "short these" mean? Do you mean "show these"? If so, is this what you are looking for: =if(and(a1<>"",a3<>"",a7<>""),true,false) Regards, Fred Best idea is to provide an example of what you want done. Regards, Fred "sunder jangra" <s...

Searching closed leads and opening originating lead from contact
I don't see anyway to modify the search fields used on the look for: function to work on closed leads. We frequently have to go back to closed leads to view notes/history that did not transfer when the lead was converted and we can't find a way to search. When I chose "closed leads" or a custom view "my closed leads" the search dialog still searches only open leads. Is there any easy way to add searching of closed views without having to do an advanced find each time? I also notice the converted contact and account has a Originating Lead attribute - is th...

How do I copy a graph and use the data in the new worksheet?
I have new data in different worksheets all with the same data format. I want to make the same graph in each worksheet using the data in that worksheet but I was hoping not to have to recreate the graph and select all the data in each worksheet. Is there a way to do this as long as the data is in the same place in each worksheet? Copy the sheet with the chart. The chart points to the new sheet's data. Copy a new set of data, and paste it over the data that the chart is pointing to, and the chart will update. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Service...

Data Validation on Numbers
How do I restraint the user from entering odd numbers in the cell, that means only allow even numbers but these even numbers must be in multiples of 5? Example : 10,20,30,40................ Thank you. Regards, Ringo Tan Ringo Tan One way: Choose "Custom" (or similar) in the validation box and enter this formula =(A1>0)*(MOD(A1,10)=0) if negative numbers are also allowed, use =(A1<>0)*(MOD(A1,10)=0) if both negative numbers and zero are allowed: =MOD(A1,10)=0 -- Best Regards Leo Heuser Followup to newsgroup only please. "ringo tan" <ringotan@di...