Macro to delete an entire row with cells missing a specific chartacter #3

Dave, 

That got the job done, but I need something that is less intensive fo
the less proficeint ppl at the office, that's why I am trying to buld 
macro.

Thanks for your help  :cool: 

Dave Peterson Wrote:
> If I were doing it manually, I'd apply data|filter|autofilter to tha
> range.
> 
> Then filter on that column using custom.
> Show the cells you want to delete
> (Does not contain maybe very useful!)
> 
> Select those visible cells (avoiding the header)
> rightclick|delete
> 
> 
> 
> asf wrote:
> >
> > Hey Harald,
> >
> > This is the script I am using to remove "IS"
> >
> > Sub Findanddelete_IS()
> > Dim rng As Range
> > Dim what As String
> > what = "IS"
> > Do
> > Set rng = ActiveSheet.UsedRange.Find(what)
> > If rng Is Nothing Then
> > Exit Do
> > Else
> > Rows(rng.Row).Delete
> > End If
> > Loop
> > End Sub
> >
> > So...my programming experience is a tiny bit limited, not sure ho
> to
> > delete anything missing the IS as opposed to containing the IS. I
> > understand in theory but my knowledge of syntax is the limiter.
> >
> > Harald Staff Wrote:
> > > Hi Asf
> > >
> > > This is far more useful if you figure it out yourself, sinc
> you're
> > > pretty
> > > close. How do you remove rows if there IS an @ in B column ? (Pos
> code
> > > or
> > > method if you're stuck)
> > >
> > > HTH. Best wishes Harald
> > >
> > > "asf" <asf.1f7sq0@excelforum-nospam.com> skrev i melding
> > > news:asf.1f7sq0@excelforum-nospam.com...
> > > >
> > > > Hi all, this forum is a lifesaver.
> > > >
> > > > Quick question, I know how to remove a row if there is 
> specific
> > > > occurrence but how do I do the opposite, remove a row withou
> the
> > > > specific occurrance?
> > > >
> > > > For example I have an email list with Col. A = Name, Col. B 
> email.
> > > I
> > > > need to remove all the rows with emails in col. B without th
> "@"
> > > > symbol (basically get rid of invalid email entries).
> > > >
> > > > Thanks!!
> > > >
> > > >
> > > > --
> > > > asf
> > > >
> > 
> ------------------------------------------------------------------------
> > > > asf's Profile:
> > > http://www.excelforum.com/member.php?action=getinfo&userid=14933
> > > > View this thread:
> > > http://www.excelforum.com/showthread.php?threadid=275429
> > > >
> >
> > --
> > asf
> 
> ------------------------------------------------------------------------
> > asf's Profile
> http://www.excelforum.com/member.php?action=getinfo&userid=14933
> > View this thread
> http://www.excelforum.com/showthread.php?threadid=275429
> 
> --
> 
> Dave Peterson
> ec35720@netscape.co

--
as
-----------------------------------------------------------------------
asf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1493
View this thread: http://www.excelforum.com/showthread.php?threadid=27542

0
11/4/2004 11:30:50 PM
excel 39879 articles. 2 followers. Follow

3 Replies
438 Views

Similar Articles

[PageSpeed] 42

Try recording one when you do it manually.

Then compare/contrast with the code that Harald wrote.  <vbg>



asf wrote:
> 
> Dave,
> 
> That got the job done, but I need something that is less intensive for
> the less proficeint ppl at the office, that's why I am trying to buld a
> macro.
> 
> Thanks for your help  :cool:
> 
> Dave Peterson Wrote:
> > If I were doing it manually, I'd apply data|filter|autofilter to that
> > range.
> >
> > Then filter on that column using custom.
> > Show the cells you want to delete
> > (Does not contain maybe very useful!)
> >
> > Select those visible cells (avoiding the header)
> > rightclick|delete
> >
> >
> >
> > asf wrote:
> > >
> > > Hey Harald,
> > >
> > > This is the script I am using to remove "IS"
> > >
> > > Sub Findanddelete_IS()
> > > Dim rng As Range
> > > Dim what As String
> > > what = "IS"
> > > Do
> > > Set rng = ActiveSheet.UsedRange.Find(what)
> > > If rng Is Nothing Then
> > > Exit Do
> > > Else
> > > Rows(rng.Row).Delete
> > > End If
> > > Loop
> > > End Sub
> > >
> > > So...my programming experience is a tiny bit limited, not sure how
> > to
> > > delete anything missing the IS as opposed to containing the IS. I
> > > understand in theory but my knowledge of syntax is the limiter.
> > >
> > > Harald Staff Wrote:
> > > > Hi Asf
> > > >
> > > > This is far more useful if you figure it out yourself, since
> > you're
> > > > pretty
> > > > close. How do you remove rows if there IS an @ in B column ? (Post
> > code
> > > > or
> > > > method if you're stuck)
> > > >
> > > > HTH. Best wishes Harald
> > > >
> > > > "asf" <asf.1f7sq0@excelforum-nospam.com> skrev i melding
> > > > news:asf.1f7sq0@excelforum-nospam.com...
> > > > >
> > > > > Hi all, this forum is a lifesaver.
> > > > >
> > > > > Quick question, I know how to remove a row if there is a
> > specific
> > > > > occurrence but how do I do the opposite, remove a row without
> > the
> > > > > specific occurrance?
> > > > >
> > > > > For example I have an email list with Col. A = Name, Col. B =
> > email.
> > > > I
> > > > > need to remove all the rows with emails in col. B without the
> > "@"
> > > > > symbol (basically get rid of invalid email entries).
> > > > >
> > > > > Thanks!!
> > > > >
> > > > >
> > > > > --
> > > > > asf
> > > > >
> > > >
> > ------------------------------------------------------------------------
> > > > > asf's Profile:
> > > > http://www.excelforum.com/member.php?action=getinfo&userid=14933
> > > > > View this thread:
> > > > http://www.excelforum.com/showthread.php?threadid=275429
> > > > >
> > >
> > > --
> > > asf
> > >
> > ------------------------------------------------------------------------
> > > asf's Profile:
> > http://www.excelforum.com/member.php?action=getinfo&userid=14933
> > > View this thread:
> > http://www.excelforum.com/showthread.php?threadid=275429
> >
> > --
> >
> > Dave Peterson
> > ec35720@netscape.com
> 
> --
> asf
> ------------------------------------------------------------------------
> asf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14933
> View this thread: http://www.excelforum.com/showthread.php?threadid=275429

-- 

Dave Peterson
ec35720@netscape.com
0
ec357208423 (183)
11/5/2004 12:41:43 AM
"Dave Peterson" <ec35720@netscape.com> skrev i melding
news:418ACC47.9E5EBF43@netscape.com...
> Try recording one when you do it manually.
>
> Then compare/contrast with the code that Harald wrote.  <vbg>

Ok, I may have interpreted the phrase "remove"  too Schwartzenegger ...
Best wishes Harald


0
innocent (844)
11/5/2004 3:39:43 PM
When I saw his code, I giggled.


Harald Staff wrote:
> 
> "Dave Peterson" <ec35720@netscape.com> skrev i melding
> news:418ACC47.9E5EBF43@netscape.com...
> > Try recording one when you do it manually.
> >
> > Then compare/contrast with the code that Harald wrote.  <vbg>
> 
> Ok, I may have interpreted the phrase "remove"  too Schwartzenegger ...
> Best wishes Harald

-- 

Dave Peterson
ec35720@netscape.com
0
ec357208423 (183)
11/5/2004 8:10:01 PM
Reply:

Similar Artilces:

Restricted Access Error with Microsoft Office 2004 SP2 and Mac OS 10.3.x
We updated to Microsoft Office 2004 SP2 and everything runs fine. However, on the 20 or so restricted access ID's that we had previously setup now encounter the infamous http://support.microsoft.com/?scid=kb;en-us;908466 error: When you start Microsoft Excel 2004 for Mac, Microsoft PowerPoint 2004 for Mac, or Microsoft Word 2004 for Mac, you receive the following error message: "An unexpected error occurred while trying to load the Microsoft Framework library." This only happens with a restricted account. We have uninstalled, cleared the startup items, startup addi...

Autofitting a row
Is it just me, or has something changed with Excel 2003? If I have a cell with wrap text, shouldn't the row size automatically increase if the text exceeds the cell size? For some reason, my text just gets squished. I know I can go to Format-Cells-Autofit, but if I do that, the row could SHRINK if I have too little text in the cell. Here's what I want: I want to increase the height of the cell to a decent size (e.g. 30). IF the text exceeds the cell, then I want it to expand. But if it doesn't, I want the cell to remain at 30 - not decrease. Is there a way to do this? ...

missing mail #3
Hi My outlook 2003 inbox has lost thousands of email, up till last few weeks. Must have hit a key accidentally, but not in deleted folder and not archived.. any other ideas thanks tim Have you checked to make sure you didn't accidentally switch your folder view(s) to a filtered view that might be hiding the older items? (You can also make sure you're not sorting on the Icon column.) Also, have you tried doing an Advanced Find to see if the items were moved to other folders? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- ...

SBS 2003 & Sharepoint 3.0 & SMTP
History: SBS 2003 R2, all windows updates, etc. Installed Sharepoint 3.0 side by side. Testing email alert features. Notice emails not going out. I am not versed with IIS at all. Tried a couple of things then decided to re-install IIS SMTP component. NOT GOOD. Found that I had damaged the IIS / Exchange Server SMTP service extensions and they had not been restored. I used KB822939 & Q320202 to resolve the problem with Exchange. (Un-install and re-install IIS and Exchange), It took two tries before it worked. However my orginial problem still exists. No emails going...

macros #4
I want to create a macro to send a reminder mail to a mail ID on a specific date on everymonth. How to create a macro to simplify my task? See if the information on the following page helps: http://www.outlookcode.com/d/forms/skedrpt.htm -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** "Maheswar" wrote: > I want to create a macro to send a reminder mail to a mail ID on a specific > date on everymonth. How to create a macro to simplify my task? &q...

Macros Won't Go Away
When I bring up my spread sheet it asks me if I want to disable macros. To my knowledge there are no macros. If I click on macros, to see what's there I don't see anything. Can I somehow get rid of that icon that comes up every time I open my spreadsheet? Thanks Jimmy You need to delete the modules that held the macros, as well as the macros themselves. Right-click on your sheet tab and select View code. In the left-hand window, it will show the modules that exist. Andy. "Jimmy W" <JW@non.net> wrote in message news:tl6n005f93e7tgdloiklcbgmukdlucnbja@4ax.com... ...

Formula doesn't adjust when Inserting a row into a range
I am building a budget using outlining in order to show detail for a particular total. I want to start out the budget with only one detail line per budget, and then write a macro to allow the user to insert a detail line. The problem I have is that when I use the insert command the formula for the total below the detail line doesn't adjust to include the new detail line. Any ideas? I would really appreciate the help. David Hi David, You should start with 2 detail lines for each subtotal. When you insert a row do it between the 2. Never insert a row as the first or the last i...

Problems installing to MS CRM 3
Installing MS CRM 3 (RTM code) with trial licence to SBS 2003 Server with SP1. Install reporting an error on SRS where the HTTP:\\SERVERNAME can't be reached. I note that the default web site doesn't have a host header - is this what is causing the problem ? Have already performed the recommended work around (deactivating loopback check) on SBS. Generally frustrated with this as we were all told that the installation was much simpler now !! ...

Need to permanently changing a row source after running code
Hello! I have added code to a form object's Not In List event to add the item to the row source automatically. While I'm in the form, the item is still there to select, but when I close the form & re-enter, the new items that I just had code add, are not there anymore & it defaults back to my original row source entries. It is not a lookup to another table or query. Just a simple value list. Any help will be extremely appreciated... I'm stumped & desperately need to wrap up this project? Thanks to everyone! Melinda S wrote: > Hello! > I have added code...

Automatically update a given cell
I would like to automatically update a given cell, ex: C:1, with whatever I put into any given cell within a range ex range D3:Z3 today I enter the number 3 into D3 C:1 is updated to reflect the number 3 tommorrow I enter the number 8 into E3 C:1 now is updated to reflect the number 8 the numbers would be entered in succeeding cells , within the range, with no cells skipped. Any help would be appreciated. Thanks Tom picktr@wowway.com --- Message posted from http://www.ExcelForum.com/ Tom, =OFFSET(D3,0,COUNTA(D3:Z3)-1,1,1) -- Earl Kiosterud mvpearl omitthisword at verizon period ne...

Public folder limits #3
I have a single public folder sore (only one allowed) that conatins a couple of calendars and some folders. I want to periodically delete all posts in the folders older than 90 days. I know there is a limits option in exhcange 2003 on the public folder store but Microsoft state you sholdn't set to delete items on calendars...so, how can I for folders, the only option on the folder itself is to delete 'replicas' after a certain number of days. On Mon, 19 Mar 2007 15:10:05 -0700, Fabrussio <Fabrussio@discussions.microsoft.com> wrote: >I have a single public folder s...

LINKING CELLS #2
HI ALL WAS WONDERING IF ANYONE COULD HELP WITH THIS MY PROBLEM IS I HAVE A DROP DOWN LIST WITH ALL DIFFERENT COUNTRIES IN I WHAT I AM TRYING TO DO IS WHEN I SELECT GERMANY THAT CELL A1 WOULD READ BERLIN AND WHEN I WOULD SELECT FRANCE THAT CELL A1 WOULD THEN READ PARIS AND SO ON, COULD ANYONE TELL ME IS THIS POSSIBLE jinx Please switch off your CAPS! It is considered bad etiquette. You could set up a table with one column containing countries and one containing their capitals. You can then use a simple VLOOKUP() function to return the relevant capital: Germany Berlin France Paris...

sum only if a certain cell contains a date
I am trying to include a figure in a sum but I only want to count it if a date appears in another cell. How about providing some details? -- Biff Microsoft Excel MVP "mimsly17" <mimsly17@discussions.microsoft.com> wrote in message news:AD685823-85F7-408C-8407-6B7FD351DEF5@microsoft.com... >I am trying to include a figure in a sum but I only want to count it if a > date appears in another cell. > > Something like: =a1+if(b1>0,c1,0) ? Regards, Fred. "mimsly17" <mimsly17@discussions.microsoft.com> wrote in mes...

bar chart #3
I am trying to make a bar chart. My problem is that excell keeps combining info from 2 different projects and inserts both of them into one bar chart. How do I fix this problem? I assume that you want to see the data as two series in one chart? Put your different projects into different rows, or preferably different columns, select them both, and the Chart wizard should split them into two different series. If that doesn't work, select one series, insert the chart, then go to source data, & add the second series. -- David Biddulph "Ann" <Ann@discussions.micro...

Cell Alias??
Is there a way that I can use one cell as a quotient for a formula in a column without editing the formula each time? Example: A12= 1.04 C15 =A15-B1 C16=A16-B1 C17=A17-B1 C18=A18-B1 Auto Fill will start filling B1 thru Bx Can I give B1 an alias that I can refer to in my formula that Autofill wont try to correct? Change the formula in C15 to =A15-$B$1 then copy down. The $ signs anchor B1 For more on this see help on absolute and relative cell refences. Gord Dibben MS Excel MVP On 21 Nov 2006 11:38:56 -0800, "Epoh Rio" <epohxcvii@hotmail.com> wrote: >Is there...

How to check a condition and write another cell
Hi, This problem has bugged me for a week, any help are appreciated. # Condition I have 5 sheets: -Sheet1 : list of text consist of 4 to 5 characters (i.e AAAD, AAAF,....ABX23) in a single column but many rows. A B C =============== 1 AXDD 2 AGHO 3 ASDL 4 ... -Sheet2 : an array contain list of possibilities from AAAA to AZZZ (column defined as A to Z to the right, row defined as AAA to AZZ to the bottom) A | B C D E F G H.........AA ================================== 1 | A B ...

Change Number to Text , Case error when cell is String and format is General
Hi All How to check the cell is text or number ? When Cell is string, Case Excel close and recoveing. Sub FormatNum2str() '~~ 2010/02/18 Dim iRows As Long Dim iCols As Long Dim ir, ic As Long Dim iNum As Long Dim strVal As String If Application.Selection Is Nothing Then MsgBox "No Open Worksheet", vbCritical Exit Sub End If iRows = Application.Selection.Rows.Count iCols = Application.Selection.Columns.Count For ic = 1 To iCols For ir = 1 To iRows With Application.Selection.Item(ir, ic) If .NumberFormatLocal = "Gene...

Delete locked task
I have a task in Outlook 2007 that I can not delete, mark as finished or anything else. It is locked tight. It gives me a reminder everytime I open Outlook and it is quite annoying. If I try to set it as finish or try to delete it Outlook gives the following message (translated from Swedish to English): "The Assignment could not be finished. One or more parametervalues is not valid." Any suggestions, I have tried to search for an answer on this forum, on microsoft.com and with Bing/Google without luck. A big thank you in advance! Alveja wrote: > I have a t...

select the last cell in a long list
hello i'm stuck i need some help on selecting the last cell ( 1st blank one available ) in a large list, there will be no gaps in the list at all. so far using VBA i've been able to select and sort my list using CurrentRegion, would like the curser to return to the end of the list so i can enter more data as and when required. i know i will be kicking my self as its bound to be something i'm overlooking. any help would be appreciated Regards DarkNight To select the first empty cell use Range("A1").End(xlDown).Offset(1, 0).Select More reliably if there could be b...

Modify 'completed' activities in MS CRM 3.0
Is there a way to modify 'Completed' Activity Records in Microsoft CRM 3.0? For example, a user needs to add some aditional text to the description field in a 'Phone Call' Activity record that has been set as "Completed." Or a user mistakenly assigned a series of Activity records to the wrong Account. Now, those records need to be modified so that they are set as "Regarding" to another account. MS CRM 3.0 sets all fields to 'grey' and un-available for editing once the activity record as been set as "Completed." So, is there a way...

Deleted Items Folder
Hi. Need some assistance with an issue in regards to my Deleted Items folder. I have Outlook 2003 XP3. Recently I emptied the contents from my deleted items folder. I've now noticed that any newly deleted items don't appear in my deleted items folder, they now seem to be permanently deleted. The "Warn before permanently deleting items" check box within the options menu is ticked. If I drag an item to the Deleted Items folder, it too doesn't appear in that folder. I have tried a detect and repair within Outlook and have also run scanpst on the pst...

Double Clicking On Cell To Edit Data
Hello, I modify existing cells contents quite frequently. Using the mouse and double clicking seems to me to be extra work. Is there any way via the keyboard to get into a cell to edit its contents? TIA Jim Mac Millan Jim Cursor to, F2 Beege "Jim Mac Millan" <fly_boy_jim@yahoo.moc> wrote in message news:OOiJF%23cLGHA.604@TK2MSFTNGP14.phx.gbl... > Hello, > > I modify existing cells contents quite frequently. Using the mouse and > double clicking seems to me to be extra work. > > Is there any way via the keyboard to get into a cell to ...

Mass Deletion of Range Names?
It's easy to create many range names all at once using "insert/name/create" for a large number of selected rows and/or columns of data having row and/or columns headings. Once those ranges are created, is there any way in EXCEL to do a mass delete of those range-names from the spreadsheet? You can clear or delete the worksheet they are on, but that doesn't delete them as range names in the workbook. You can also go to the menu and "insert/name/define" and then proceed to delete each range-name individually, but it won't let you select and delete all...

How to know the correct height of the row
I have the worksheet in which all the margines are set to 0 (i.e. left, top, bottom,right,header,footer). Then I apply the folloing settings Format>Style>Modify>Font select Courier New, Reqular, 10. File>Page Setup>Paper size : A4 and Print quality : 600 dpi. Now my worksheet contain 73rows/page. Each row has 13.50 points height. so page height is 73*13.50 = 985.5 points But the standard A4 paper size height is 842 points. So how the excel fits the 73 rows of each height 13.5 points? the way you are writing the code it will only work with certain printers ...

Rules keep getting deleted
I use Outlook 2003 and I synch with my laptop using synchpst. When I create rules in Outlook after a week or so when I receive mail an error message will appear that states outlook cannot move message to the folder that I set up the rule for. When I check the rule the folder reverts back to unspecified folder with an error icon. I should also mention that I transfer my PST files to the laptop and bck to the desktop to ensure full synch. Any idea why this happens? Win XP Pro system Kitaman kita@kit.com <kita@kit.com> wrote: > I use Outlook 2003 and I synch with my laptop usin...