delete rows when any cells in column A is filled in RED color

Hi people,

I need your help for a VB code that can delete the rows with any cells
in column A being filled in RED color ?


Alternatively, it will be fine for me if you have the code  that can
move the rows to the top when there are cells being filled in RED color
in column A .


Thanks in advance for help.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements
0
12/2/2003 4:30:07 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
539 Views

Similar Articles

[PageSpeed] 26

If the colors are from formatting (not conditional formatting), you can use a
userdefined function that returns that color in a helper cell/column.

Then you could sort and delete.

Chip Pearson has the code for returning the value of the color at:
http://www.cpearson.com/excel/colors.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

William Poh Ben wrote:
> 
> Hi people,
> 
> I need your help for a VB code that can delete the rows with any cells
> in column A being filled in RED color ?
> 
> Alternatively, it will be fine for me if you have the code  that can
> move the rows to the top when there are cells being filled in RED color
> in column A .
> 
> Thanks in advance for help.
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
> 
> ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/3/2003 1:50:39 AM
Reply:

Similar Artilces:

How can I insert a cell reference in a footer (eg for variable foo #2
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = .Range("A1").Text End With End Sub This code should go in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "wngg001" <wngg001@discussions.microsoft.com> wrote in message news:8A0F9D9E-269F-45CF-A6E3...

VBA
I have created code that inserts lines into a financial statement but need to have certain cells in other code stay constant. B1 in certain code must stay at B1 regardless of changes to the spreadsheet. Any suggestions?? --- Message posted from http://www.ExcelForum.com/ Use the INDIRECT function. E.g., =INDIRECT("B1") The B1 will remain so regardless of inserting and deleting rows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "bforster1 >" <<bforster1.1cmxzp@excelforum-nospam.com> wrote in m...

First non-blank row in WS after hyperlinking
I have several ws I use as check registers. I'd like to be able to go to the last non-blank row in that ws when I click that register's ws from the index page. It would always be the next available row in the applicable ws. Is there a way from HYPERLINK to do that or maybe a function once I get there? Macro? TIA here are a couple different ways: this will select the cell. it's not a good practice to select, but just used as an illustration here. range(worksheets("Sheet1").cells(rows.Count,"A").end(xlup).address).Select Sub FindLastCel...

Publisher 2007 -how to view all pages in bottom row of taskbar
I've upgraded from 2003, and now the pages of my webpage listed on the bottom of the Publisher window are all missing (my website pub. document contains 10 pages, which were visible as number rows at the bottom underneath the document within the publisher taskbar frame). I cannot select a page any more. How can I get this page row back? Thank you in advance for your replies. CosmicFaery wrote: > I've upgraded from 2003, and now the pages of my webpage listed on > the bottom of the Publisher window are all missing (my website pub. > document contains 10 pages, which were ...

Macro Help/Duplicate Items + Insert Rows + Sum
I am trying to create a template that will do the following: 1. Find Duplicate Entries (AlphaNumeric) In A Column 2. Insert 2 Rows Between The Duplicate Entries Then: 1. Sub-Total(Another Column With Random Numbers) Of The Duplicate Entries 2. Format the Sub-Total In Bold I have gotten to the point of writting a macro that will identify the duplicate entries; does anybody know how to do the rest? This is a changing set of data, transferred to excel from a relational database (Lotus123 Rel2, which contains anywhere between 3000 to 5000 rows. I cannot spend time grouping the data ...

Add Text Field Columns
PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) I have two fields both of which are text. When I try to add them as above, I get a concatenated result, not the sum. How does one add them? -- On Fri, 18 May 2007 17:38:49 -0600, bw wrote: > PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) > > I have two fields both of which are text. When I try to add them as above, I get a > concatenated result, not the sum. How does one add them? How does one add "This is text." + "this is also text&qu...

Re: Two-Column Problem
After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Charley Kyd <kyd@incsight.com>... > But now, when I add a pair of pages, Publisher gives me guides for two > columns per page but gives me only one page-wide textbox per page, > not two. Can't you create your text boxes yourself? Or change the text box that appears to a two-column one? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm ...

Delete all records from CRecordset
Hello, How can I delete all recodors from a CRecordset? Is it possible to use a SQL clause like "DELETE * FROM my_table"? Thanks! This may help you get started: http://msdn2.microsoft.com/en-us/library/z5139kz6(VS.80).aspx Tom "Tulio" <Tulio@discussions.microsoft.com> wrote in message news:FEAB1327-8C93-44EF-9527-E1F4DFB56A82@microsoft.com... > Hello, > > How can I delete all recodors from a CRecordset? > Is it possible to use a SQL clause like "DELETE * FROM my_table"? > Thanks! There are two ways: 1. write a loop to step through th...

Column spacing
Is there any way I can control the spacing between the columns in the layout guides (chosen from the Arrange menu)? I want to set 0.7 cm column spacing for a text frame split into 3 columns and see the guides for this setting so that I can then use individual text frames on a second page. I know I can set the column spacing in the text frame formatting option but this is not reflected in the layout guides. Please can anyone help? I am using Publisher 2000. I have this work around - I make my own ruler. Make pull a rectangle, looking at the "size" indication in the lower r...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Deleting over-large outgoing mail
I accidentially attached a 6 MB file to a mail item that was to go out on my Hotmail account. Now that it's in the outbox, Hotmail won't take it (it's too large) and I can't delete it (permission denied). Any help would be appreciated. Thanks in advance, J. Create a new .pst file and make it the default. Close and reopen Outlook. Go into your outbox and delete the message. Close the new .pst file. Close and reopen Outlook. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my pers...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

Cell QA for Bob Phillips
Good afternoon Bob: In reference to your QA: (Which sheet was the active sheet when you right-clicked on the tab? It should have been Data Sheet). Yes, I did as you said, but I am not sure I placed the text string in the right place. I pasted it in View Code on the blank area to the right of "Project / Properties" section but for some reason it is not moving the text from "Data Sheet" to the "Activity Sheet". Is it possible to communicate with you directly so I can get this worked out? Thanks John ---------------------------------------------------...

Change color on date field in form for re-certification
My main form has two different date fields on it. One date field needs to be re-certified every 6 months, and the other field needs to be re-certified every 12 months. I would like each of those fields to automatically change color depending on how much time as elapsed since that training. For the 6 month field, months 1-5 should be green, 1 month left should be yellow and anything expired should be red. For the 12 month field, months 1-11 should be green, 1 month left should be yellow, and anything expired should be red. Thank you in advance for your help! messingerjc wrote...

need Blank column in invoice
Hi, How can I make(or hide) contents of cell unless the cell before it isfilled in? Hope i said that right. My invoice has a payment col and balance col next to each other,. I want to have the formula in the balance column all the way down, but it should be emty until a payment is filled in? Thanks Frank Frank Trap for no entry in the payment column by entering a formula in the balance column. =IF(payment_cellref="","",balance_formula) Gord Dibben MS Excel MVP On Wed, 19 Apr 2006 21:48:18 -0400, "Frank" <stratster68@IHATESPAMworldnet.att.net> ...

reverse columns and rows
I have decided that my rows and columns should be reversed, for printing. Is there a way to print this out so that they are in reverse or do I need to manually change everthing within my spreadsheet? Hi Teri. I'm afraid you have to change everything. But, it's straight forward. Copy your data, select the first blank column in Row A and do Edit>Paste Special>Transpose and then delete the original data columns. Also, if you just want to do it for printing, you could use a blank spreadsheet for the pasting. HTH -- Sincerely, Michael Colvin "Teri from Florida"...

How do you change to currency in a table and add columns?
I'm trying to learn to use the new Word and can't figure out in my ribbons how to change numbers to a currency format, as well as viewing the table content to be able to quick sum columns. Help?! Use Excel which is designed for that work. -- Terry Farrell - MSWord MVP "Duprey72" <Duprey72@discussions.microsoft.com> wrote in message news:ADB852A4-D4AF-44FF-8F89-AC4B110D080C@microsoft.com... > I'm trying to learn to use the new Word and can't figure out in my ribbons > how to change numbers to a currency format, as well as viewing the tabl...

how do I get the if function to return a blank cell, not 0?
I am trying to create a chart from a series that contains data for each month. The series is calculated on other worksheets and copied to the worksheet containing the chart. I would like to have the cells for the months that have not been updated yet (now is January, there are 0' in all cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's. Can this be done? Unfortunately, what you want, and what many of us have requested but doesn't exist, is a worksheet function like BLANK() or NULL(). The best we can do is use NA() in a chart's data source, which is...

Deleting of Recurring E-Mail's Outlook 2000
How do you delete recurring e-mails in Outlook 2000? Do you want to keep the history of the recurring? Do you want to "Cancel" future meetings and let other recipients know? Do you just want to delete it entirely? -- Nikki Peterson [MVP - Outlook] <gary.christian@carswell.af.mil> wrote in message news:e42601c43c4c$5aca1270$a601280a@phx.gbl... How do you delete recurring e-mails in Outlook 2000? ...

Help on Macro to hide empty rows
Hi, I have a spreadsheet I created for an administrator that has many extra rows with pre-set formulas. When we print though, there are a lot of empty rows in between the relevant data. I am trying to build a macro that will hide any row where column A is empty, then print, and then unhide them again. Below is the macro I have so far. But it does nothing! Any help or suggestions are appreciated as I haven't written macros in years. (I have latest version of Excel on Windows Vista.) Sub PrintOrmondBeach() ' ' PrintOrmondBeach Macro Sheets("Ormond").Select D...

Money 2007 delete previous versions?
I'd like to try Money 2007 trial before committing. I'm using Money 2004 SB. Does the upgrade wipe out any previous version so that I can't go back if I don't want to commit to the upgrade? I'm running XP SP2. Thanks, Brett It will give you the chance to select yes or no to remove it Steve -- Steve steven_morrison69@hotmail.com "brett" <account@cygen.com> wrote in message news:1163964503.767879.204210@m73g2000cwd.googlegroups.com... > I'd like to try Money 2007 trial before committing. I'm using Money > 2004 SB. Does the upgrade w...

How to slot cell values into pre-defined ranges
I would like to slot cell values starting from 500 upto 75000 in ranges (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have 15 bins. pls help Not sure what you want here. Do you want to count the number of times values within a range occur? -- Ian -- "KDD" <KDD@discussions.microsoft.com> wrote in message news:766017CE-A55E-42FF-AD8D-9E74E48CD568@microsoft.com... >I would like to slot cell values starting from 500 upto 75000 in ranges > (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have > 15 > bins. > > pls...

Recovering deleted items #3
Hi, I am encountering a strange problem. I am trying to recover mails which were deleted permanantly from my mailbox. After editing the registry settings i am able to recover all folders except deleted items. While I click on deleted items folder to recover the items outlook exits. Has anyone come across this problem? Thanks Shilpa hoe many items are in the deleted folder? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Ou...

How can I protect certain columns on a sheet so that formulas are.
How do I go about protecting certain columns on an excel sheet so that formulas are not changed by someone else entering data onto the worksheet Right click on the cells you want to allow data entry in. Then select "format cells", Then "protection". uncheck the "locked" box. Then protect the worksheet. "Tan" <Tan@discussions.microsoft.com> wrote in message news:B7E54609-EA18-4882-8106-7B954F0C3F8B@microsoft.com... > How do I go about protecting certain columns on an excel sheet so that > formulas are not changed by someone else entering...

How to change default cell formats
When I open a file, all negative values are shown with a - before them, whilst my colleagues, when opening the same file, the negatives are shown in brackets ie. ( ). How do I ensure that when I open the same file, my negative numbers appear in brackets This is a windows setting. Close excel Change that windows regional setting|currency tab|Negative currency format to show ($1.1) Open excel You may have to reformat: Format|Cells|number tab|Number (adjust the decimal places and comma options) Easty04 wrote: > > When I open a file, all negative values are shown with a - before t...