delete row if given cell = 'X'

Is there an easy way to do the above.

Thanks much


nf9641 (3)
9/26/2009 9:34:03 PM
excel 39879 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 31

"MSNews" <> wrote in message 
> Is there an easy way to do the above.
> Thanks much
> Carl

I'm not an expert but use data filter to select the rows, select and delete 

Try it with a trial sheet first though!


p.andrews (27)
9/26/2009 10:10:12 PM
Several ways
1. data>filter>autofilter>filter on the x>delete
2. a looping macro from the bottom up

sub delifx()
for i=cells(rows.count,"a").end(xlup).row to 2 step -1
if lcase(cells(i,"a'))="x" then rows(i).delete
next i
end sub

Don Guillett
Microsoft MVP Excel
SalesAid Software
"MSNews" <> wrote in message 
> Is there an easy way to do the above.
> Thanks much
> Carl

dguillett1 (2487)
9/27/2009 12:55:18 PM

Similar Artilces:

How to add cells wiht the condition of the former cell.
For example: If the previus cell has a "5". How can I add all the cells whose previous cell is a "5". How can write this in a correc way in excell. Hi do you mean =SUMIF(A1:A100,5) -- Regards Frank Kabel Frankfurt, Germany "Rafael" <> schrieb im Newsbeitrag > For example: If the previus cell has a "5". How can I add all the cells > whose > previous cell is a "5". > How can write this in a correc way in excell. > > >...

Outlook calendar auto-delete
My outlook calendar is automatically sending appointments to my delete folder after 60 days which is a problem since I use that calendar to keep track of my business. I cannot figure out why it is doing this. I have read that my BB may be causing this. I called my BB provider T-Mobile and they had never heard of this problem. I changed my BB setting to keep calender entries "forever" but it still seems to be happening. I would appreciate any thoughts on what to do next... Submitted via EggHeadCafe - Software Developer Portal of Choice Featured Product / Service Review: ...

delete page within a worksheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Does anyone know how to delete all the extra pages of rows an columns that show up in excel within a worksheet? <br><br>I don't know if these are extra pages but when I scroll down or to the right, it just continues adding them forever. <br><br>I don't want to to set a print area. <br><br>I want to delete the extra sheets. <br><br>Phil The size of an Excel worksheet is fixed - you cannot remove the unused columns & rows. Their presence has no bear...

Delete messages in Imap folder
I'm looking for a rule who can move messages from Imap folder to other folder (for example "delete Item" each time i delete message. Thanks, ...

excel worksheets getting deleted in shared files on P4
Hi all I am using a workbook of 3 Mb on a network where I am using Excel 9 and MS Windows. File contains 32 worksheets for a month. Network trafic is more but th comp has 512 MB ram and uses some oracle app also. The files are sometimes shared and contains some 3d as well as arra formulas. The worksheets are getting deleted automatically when we are trying t save the file. What can be the possible reasons. Why do sheets ge deleted????? Thanx in advance Keda -- Message posted from ...

Cell in Sheet one automatically going to a cell in sheet two
Hey again...I have two sheets in my book. I would like the amount that is in E26 in sheet 1 to automatically pull up in G13 of sheet 2. Can I put in a formula to do that? Thanks. -- Roz ------------------------------------------------------------------------ Roz's Profile: View this thread: In G13 of sheet two, enter: =Sheet1!E26 HTH, Paul "Roz" <> wrote in message news:Roz.212oym_113631540...

Office for Mac X and new iMacPro
Got a new iMacPro. Using firewire, copied everything, including Office Mac X, to iMacPro from old iMac (not a laptop). When I tried to use Word or Excel from either the dock icons or files, Office Test Drive Word or Excel showed up. So I put Test Drive in the trash, deleted Test Drive icons from dock and replaced them with icons linked to Office Mac X. All is fine except for one glitch: can't open files; have to open application and then open file from the ap. I called Apple help. Was told to remove all Office applications, X and 2004 Test Drive, from my computer and then re-install...

Delete Page Number Header
I created a 12 page newsletter in Pub 2002. I don't want the page number to appear on the top center of the last page since it is blank. How can I do this? Thanks. maven -- CONFIDENTIALITY NOTICE: The information in this message, and any files transmitted with it, is confidential, may be legally privileged, and intended only for the use of the individual(s) named above. Be aware that the use of any confidential or personal information may be restricted by state and federal privacy laws. If you are not the intended recipient, do not further disseminate this message. If this messa...

display a drop-down list based on the content of another cell
I want to display a drop-down validation list for a target cell only if the vlaue in another cell meets or exceeds a numerical threshold. If the threshold is not met, I want to force the target cell to remain blank. Cell A contents -----> Cell B contents < 100 must be blank >= 100 may only be "red" or "green" or "blue" Using MS Office Excel 2003 Joe, without VBA you can have a Custom data validation with the following formula: =IF(A1<100, B1="", OR(B1="a", B1=...

Hi All, Whenever I search for something (CTRL+F) in excel it always defaults to the "Find entire cells only" setting. Is there any way I can change this default setting - I want it to search for any cell , not whole cells, by default. Thanks Andi I use xl2003 and when I first open excel (with a blank workbook), I hit ctrl-f, then that option is not checked. But excel likes to help. It remembers the previous settings that you used--including any VBA code that you may have run. Are you sure that it's the default and not the result of a previous find? Andibevan wrote: >...

Always show the content of a specific cell in the frame of Excel
Is it possible always to show the content of a specific cell in the frame of Excel (so you always can see it). Then it is possible always, for examble, to see the sum of some cells, when you are filling the underlying cells with numbers. In QuattroPro ver. 8.0 this was possible. In Danish this cell was called "Lyncelle" - perhaps "Quick-cell" in English. Best wishes Niels Danstrup Excel 2002 and 2003 have a Watch Window toolbar that displays the value of one or more cells. That may be what you want. -- Jim "Niels Danstrup" <NielsDanstrup@discussions...

I am new at cell "if" formulas ...please help
I have just started understanding and using the "IF" function, and I seem to be doing very good ...except for this one problem that I can not figure out how to format the cell. Please help! I am pulling information from one cell of another worksheet to this worksheet. It is hours worked per day per employee, however 2 employees are salaried but, are paid for 40 hours. So what I am trying to set the cells that are pulling the hours worked per day to show and keep the formula from week to week since they might not work the same number of hours, or the same days. If it is po...

cell to change color =IF(B7>53,"Critical","Normal")
I need to have a cell change color or flash when the water level reaches a certain critical depth on a pumping spreadsheet so the operator is aware of a problem! To change the background color of your desired cell: select that cell then click Tools>Conditional Formatting... on the main menu. In the Conditional Formatting dialog box that opens, choose Formula Is: and enter this in the formula line: =B7>53 click on the Format button, click on the Patterns tab and choose your desired fill color (with Red fill I also like to change the font color to Yellow and make it Bold-use the Fon...

How the heck do I plot 'my text' for the X axis? (instead of 1, 2, 3, 4 etc.)
Hi, I'm using XL2002 & Win2000Pro. How can I display the week numbers (as shown) in the x axis, rather than 1, 2, 3, 4 etc. that M$ uses. Here's my data Week 11 12 13 14 Sales A 43 55 48 32 Sales B 32 36 41 29 Here's what I want the axis's to look like 50 40 30 20 10 0 11 12 13 14 How do I do it? Many thanks!!! Norm Father Guido ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ I plan on living forever... so far, so good Option 1: Del...

Counting Cells which are shaded a certain colour
Hello, I have a spreadsheet set up as a year calender for booking annual leave at work. Each staff member shades cells their own colour to book annual leave. I'd like to set up a function which counts the total number of cells shaded a certain colour - so when someone adds leave, their own personal running total is displayed. Hence I need a formula which counts the total number of cells in the worksheet which are a certain colour (using the standard palette). I'd be very grateful if anyone could let me know if there's a simple way to do this? TIA for any help Paul Paul, have...

How to add fill colors (cell shading) in EXCEL?
When I go into Format>Cells>Pattern, there are squares of custom colors just below the "standard" colors. I'd like to add more custom colors, but don't know how. Maybe they came from something I cutomized in Word or another Office appilcation. Can anyone tell me where these colors came from and how to create more? Go to Tools>Options>Color, there is a Modify button there that will allow you to change one of the colours. It can be one of the 16 'custom' colors, or even any of the other 40. -- HTH RP (remove nothere from the email address if mail...

First server in a site refers to deleted server
Hello all, Recently introduced our first Exchange 2003 server into a Exchange 5.5 site. Originally we had 2 Exchange 5.5 servers EXCH and EXCHG2. After we introduce the new Exchange 2003 server we followed the instruction on Q152959 to decommision EXCH. We moved all mailboxes, Offline Addressbook, Public system and user folders to the new Exchange 2003 server EXCH2K3. We then removed the EXCH server from the site. As far as I can see everything is working fine. What is a bit troubling is the fact that I still see a reference to the EXCH server(decomission Exchg 5.5 Server)as the Site folder s...

column and rows meet
Aviation chart for takeoff field length. I have 2 columns and 1 row. 1st column are elevations. Sea level to 14,000 ft in 1000's of ft. (Sea level, 1000,2000, etc.) 2nd column are weights . 14,000 to 21,000 lbs in 1000's of lbs (14,000, 15,000, etc) first row across the top are temperatures. -40C to 50C in 10c increments (-40, -30, etc). I will have cells labeled Elevation, WT, and Temp which will be manually inputed (keyboard). end result will be distance required for takeoff. Below is a fraction of the actual table. ALT / FT WT / LB �C -40 -30 14000 2800 2900 15000 2840 2940 1...

CONCATENATE problem with blank cells
I need to merge columns A and B, but some cells are blank, so I can't copy the formula down the 3rd stops at the 1st blank a1 Frank Smith a2 blank a3 blank a4 Bill Jones b2 blank b2 sally b3 billie b4 blank HELP? -- roger_home ------------------------------------------------------------------------ roger_home's Profile: View this thread: Hi Roger, You could Concatenate the Cells in Columns "A" & "B" Using the Follow...

Automate unprotecting of blank cells
I have a spreadsheet that has several cells with values entered int it. I want others to add some values, but I don't want them to change cell that already have values. I know that I can use goto, special, constants and protect thos cells. Can someone recommend a macro or method to review each cell within specified range and unprotect it if it is blank? Any suggestions would be appreciated. Jim Palme -- Jim Palme ----------------------------------------------------------------------- Jim Palmer's Profile: View t...

Network Folder delete
Our AV solution makes temp files on all our servers in the Windows directory. Im looking for a script I can run locally that reads from a serverlist.txt -> goes out and deletes all folders and their contenets in the following directory: C:\WINDOWS\SoftwareDistribution\ScanFile - except for today. I've searched high and low and seen pieces, such as reading over the network, searching for files based on date, deleting files (not folders), but nothing quite like my request. Any scripting help would of course greatly be appreciated! Thanks! Let me add the following proces...

Date cells after conversion from Database
In our organisation we are dealing with different date formats (dd/mm/yyyy or mm/dd/yy) When information is requested out of our JDE database it is presented as a csv file. After convertion into Excel the 'dd/mm/yyyy' are recognised as date formats, but the 'mm/dd/yy' only as general formats. How do I change those last ones in recogniseable date formats? Thanks for your help!!!! Rene. if you can highlight them all then right click and choose format cells then under the number tab choose custom then enter mm/dd/yy in the to box. that might help cheer -- Message po...

Cell.Find in VBA
Hi, I have the following VBA Macro: Set FoundCell = .Cells.Find(What:="199", _ After:=.Cells(1), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) I don't how to write a VBA macro that would cells.find all 5-digit numbers. Here's an example: Column A 199 199 75781 75899 199 80012 Thanks, Hi I think you have to loop through all cells and check the length/value of each cell -- Regards Frank Kabel Frankfurt, Germany "Jeff" <> schrieb im Newsbeitrag news:D3CEA329-2A...

Change cell colour if.....
Looking to have certain cells that are unlocked, to change to yellow background - Else, stay or change to standard white background. To check / apply to complete work area of worksheet. This should allow me to run macro whenever I want, in the event that cell locking or unlocking was changed. Assuming this would be a macro, I would set this up to to operate from a button on my custom personal macro toolbar. John F. Scholten Hi why don't you check in a macro directly the locked state of the cells? or even unprotect the sheet before executing the code? -- Regards Frank Kabel Frankf...

Problem copying cell contents
I have a worksheet called Input which has text in cells A3, A30, A57, A84 etc which I want to copy to cells B4, B5, B6 etc of a worksheet called Creditors. I can type =input!A3 into cell B4 of the Creditors worksheet and get the correct answer but if I try to copy this to B5 I get =input!A4. Please can anyone tell me how to copy this but increment the cell reference by 27. Hope this makes sense! Thanks for your help. Hi Try =INDIRECT("Sheet1!A"&(ROW()-4)*27+3) Regards Roger Govier QJ wrote: > I have a worksheet called Input which has text in cells A3, A30, A57, A84...