VBA Sub to delete rows based on a Column Value

I have a workshhet named Final Short Quote. Anywhere in Column C wher
the cell conatins the word Remove, I want to delete that row. How can 
do that

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 5:55:40 PM
excel 39879 articles. 2 followers. Follow

2 Replies
641 Views

Similar Articles

[PageSpeed] 4

Hi
try the following macro:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
    If instr(Cells(row_index, 3).Value,"Remove")>0 then
            Rows(row_index).delete
     End If
Next
Application.ScreenUpdating = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


> I have a workshhet named Final Short Quote. Anywhere in Column C
where
> the cell conatins the word Remove, I want to delete that row. How can
> I do that?
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
7/23/2004 7:52:21 PM
I have also some examples on this page
http://www.rondebruin.nl/delete.htm

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:%23Tek95OcEHA.3300@TK2MSFTNGP09.phx.gbl...
> Hi
> try the following macro:
> Sub delete_rows()
> Dim lastrow As Long
> Dim row_index As Long
> Application.ScreenUpdating = False
> lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
> For row_index = lastrow - 1 To 1 Step -1
>     If instr(Cells(row_index, 3).Value,"Remove")>0 then
>             Rows(row_index).delete
>      End If
> Next
> Application.ScreenUpdating = True
> End Sub
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> > I have a workshhet named Final Short Quote. Anywhere in Column C
> where
> > the cell conatins the word Remove, I want to delete that row. How can
> > I do that?
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
7/23/2004 8:17:19 PM
Reply:

Similar Artilces:

Search within folder name using VBA
Hello, I have a folder structure in Windows Explorer, every folder has its own customer number. The user should be able to type a substring of a customers number in an Access form and I want search within the folder names and return the folder paths. How can I search within the folder names?? i found many articles about searching files but none of folder names. I'm very happy about every suggestion. Thank you, Walter ...

Deleted exchange folder
I accidentally deleted my exchange folder. Recovery pgms not working because it was on the server. Is there ANY way to get it back??? IT dept is ignoring my pleas to restore from backup. Please help me!!!!!!!! Since the mail still sits on the server, just add back your Exchange service. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, dianeclark asked: | I accid...

Can't delete Investment account
I have an investment account with roughly 3000 transactions in it. I'm trying to delete it, since money didn't classify the transactions correctly (apparently doesn't know about Short Sales)... so my balance is completely off. When trying to delete the account, I'm getting the following error: Product: Money ID: 34 Source: obres Version: 14.0 Symbolic Name: errUnknown Message: This operation cannot be performed. The recommendation to fix this is to run the file repair tool, which I did, but that only reported that no records are broken and the same error occurs i...

Can Excel find a cell based on two criteria?
My worksheet does not have column headings, but I do have unique data in a particular row. The data is a Date. The rows contain unique entries also (2 characters, always unique example JO,WO,XX,RR,TV ETC..) Is there a way, to have excel find the cell that would be in the column that contains a date (entered by user)and the Row that contains the initials (again entered by the user)? If so, can a userform be created for the user to enter the information (date mm/dd/yy) + (XX), along with the New data that goes into the 'intersecting' cell? c d e ...

Project Server 2007
We have the need to assign a "Role" to individuals on each project. Their role may vary from project to project but we need to capture the information for reporting purposes. These roles may be Project Lead, Technical Lead, Contract Admin, etc. There may be more than one resource assigned to each role. I would like to create a lookup table associated with a Project Custom Field that allows multiple selections for each Role. Rather than typing in everyone's name into the lookup table (and maintaining the same information in two places), I'd like it to pull from the Enterpri...

Convert rows to one single long column?
If I have a bunch of rows with say 5 columns each, is there an easy way to convert it to one big long column, just adding each row below the information from the previous row after being converted to a column? Copy and then Paste Special Transpose doesn't quite do it since it makes multiple columns. -- Spalding ------------------------------------------------------------------------ Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8634 View this thread: http://www.excelforum.com/showthread.php?threadid=486720 You can use a formula, assuming that...

How do I add a column to a sum formula for every row ie row 1 nee.
I need to add a column value (ie column L) to every row. Ie row 1 formula is sum(A1+B1+C1+D1+E1+F1......) I need to add +L1 to this formula. That is the easy part. Can I add L1, L2, L3 etc to there corresponding rows at once or do I have to do each one individually. Ie I have to then manually go to row 2 to add L2 then row 3 to add L3 etc. Looking for a shortcut If you select the cell with your formaula in it there should be a drag handle on the bottom right corner, just left mouse click and drag down the rows required formula will reference automatically. Hope this helps "ba...

Column heading changing
I have a delegate who has a spreadsheet that when he has emailed it to a collegue the A, B C, etc on the columns has changed to 1,2,3 etc. This only happens on 1 computer the others are fine. Has anyone seen this problem before and knows how to change it back??? Thank in advance JudithJubilee Hi Go to Tools / Options / General and uncheck R1C1 reference style -- Andy. "JudithJubilee" <anonymous@discussions.microsoft.com> wrote in message news:17e2e01c42226$6c66baf0$a001280a@phx.gbl... > > I have a delegate who has a spreadsheet that when he has > emailed...

Hide rows if they equal...
I want to use VB to write a code that will allow me to hide rows in the range A1:A100 if they say "not used". Any help? Thanks, Brian --- Message posted from http://www.ExcelForum.com/ Hi Brian One way : You can use Data>AutoFilter Choose custom in the list and use does not equal "not used" -- Regards Ron de Bruin http://www.rondebruin.nl "Rutgers_Excels >" <<Rutgers_Excels.15wgzy@excelforum-nospam.com> wrote in message news:Rutgers_Excels.15wgzy@excelforum-nospam.com... > I want to use VB to write a code that will allow me to hide ...

Auto Calendars in VBA
Need to create a monthly schedule for work.To enter the dates (Numbers) into every other cell this is what I have so far Activecell.Select Activecell.Offset(0,2)=Activecell+1 I'm wondering is there an easier way to do this.Right now I have one module for the Year 2006 with sub's for each month.These will get called when form dropdown list show's that month. if frmMonth.cboMonth.Value="January" & frmMonth.cboYear.value="2006" then Call Year2006.Jan06 end if Is there an easier way to determine the today's date then creating Next month's c...

Sorting across columns in Excel 2003
I'm using Office 2003 and the Excel module. I have a spreadsheet with 3 columns. Columns A and C include names and phone number of people. Column B is just a blank divider. If I have the sheet alphabetized, with 10 names and numbers in column A and 10 in column B, I occasionally will go to the sheet and add someone else's name. I add it either at the bottom of column A or C. I then highlight A and C and ask to have it sorted alphbetically. Can't do it!! Only thing I've been able to do is "cut" the 10 names from column C and paste them under the 10 names in col...

Select rows and sort based on type
I have a spreadsheet that contains data for several different types of accounts. I want to sort by sheet differently depending on the type of account (types are 1, 2, 3, and 4). The spreadsheet is currently sorted by account type. I want to sort further in the following way: if the account type is 1, I want to sort by account name. If the account type is 2, I want to sort by account number. If the account type is 3, sort by account number. If the account type is 4, sort by account name. I'm just guessing - but it seems like I should write a macro that will select all rows, f...

40 column reciept + Extended Description
Hi Is it possible to get a 40 column receipt to print fairly long extended descriptions in a paragraph form underneath the line item barcode and standard description. Any clues to do this would be great. Our terminals use OPOS to control the receipt printer Regards Dan Hi Is the lack of reply indicative that this is impossible (or another bug)? "dan" <danl@aanet.com.au> wrote in message news:%23t5$Q%231KIHA.3400@TK2MSFTNGP03.phx.gbl... > Hi > Is it possible to get a 40 column receipt to print fairly long extended > descriptions in a paragraph form underneat...

FRX report has extra column in GP
When we created a Frx Report in Great Plains we are getting an extra column when we print the report. It is a column of 0.00 on each line. When looking at the report on screen the zeros are not there. Where are they coming from so I can get rid of them Thanks Barry Here is something you can check in the Row format is that you can specify the column you want the number to appear in. Another item you can look into is the Advanced settings in the catalog for this report. See if this helps Kevin The Precipio Group LLC "Barry L" wrote: > When we created a Frx Report...

Get rows from another tab where cell in column is empty
I have a spreadsheet where I track amounts owed to subcontractors and when they were paid. I'm trying to create another tab where I pull all rows that have not been paid. The only thing anyone has suggested so far is autofilter but that isn't what I'm looking for. Is there a function for this or would it have to be a macro? I don't really want to do a macro. I might just build this in Access, but I thought I'd try this angle first since it's already set up in Excel. I really appreciate any help that could be provided. gg If you don't like AutoFilter, then a ...

cannot delete record...
hi, i have received an error when i tried to delete a closed invoice; "the record cannot be deleted because it is read-only" any ideas? Do you have permissin to delete? "Orkun Goze" <orkung@nospam.mostint.com> wrote in message news:OtgRKZioDHA.1672@TK2MSFTNGP09.phx.gbl... > hi, > i have received an error when i tried to delete a closed invoice; "the > record cannot be deleted because it is read-only" > any ideas? > > Yes Darrin. I am the system admin. "Darrin Bishop" <dbishop@lrs.no.sp.am.com> wrote in message news:eI...

Using VBA, how to write unique solutions only?
Hi everyone, I run an optimizer n times, say 10, and tell the model to write feasible solutions into an excel table as follows: RUN Cost Weight Volume 1 2.3 51.27 20.3 2 3.1 49.2 22.2 3 4.3 37.2 16.7 4 2.3 51.27 20.3 5 6 and so on. I use a piece of VBA code like this: Set ARngSolution = Workbooks(filename). _ Worksheets("ParetoFrontier").Range("SolOBJSens") ARngSolution.Clear Cline = 1 For JobNr = 1 To 10 result = vehicleModel.Rea...

charting multiple identical x values overlapping
A B red 1 blue 2 orange 3 red 4 If I graph the above set of data in a bar chart, with column A being the x-axis and column B being the Y axis, I'm having issues finding a way to format how excel handles multiple instances of the same x value ('red' in this case). Currently, it displays both '1' and '4' merged together in the same bar. When you look at it, the bar just looks messed up rather than like two distinct entries. Ideally, they would be displayed as two separate side-by-side bars, or, even better, with a combined value of 5. Is there any w...

Delete Button Code needed !
Before I can delete a client out of my SubForm I want a code to check that it has at least 1 other client other wise you can not delete the last client connected to that horse Table is: tblHorseDetails The 3 fields are OwnerID , HorseID & OwnerPercentage What I want is when I delete OwnerID from that HorseID (I need a code to check that, that HorseID has at least 1 OwnerID) (Never Mind about Percentage) What I am trying to do is to stop a Horse having no OwnerID because it causes HAVOC later on ---------------------------------------------------------------- Current Dode: Private Su...

select legend pattern for individual values in an excel doughnut.
Hi, Using Excel 2003, I'm making doughnut charts. (Doughnuts, Ummm....). I want to customize the legend values. I especially want to distinguish different values with different patterns, since I can't afford to have all my reports reprinted in color. Right now, I can change the legend, but when I make a change it affects the entire doughnut ring, not the individual values. Marsh - It takes two single clicks on the bite of donut to select it, so it alone gets the new formatting. Alternatively, two single clicks to select first the legend, then the legend key (the colored square...

How to format a range of numbers (single column) to time format in MS Excel
Guys, Can any one of you tell me how I can convert or format a range o numbers in a single column into a range of times (time format)? Is i possible? Example.. 0945 - 1000 is to be converted to 9:45 - 10:0 -- Message posted from http://www.ExcelForum.com Hi Sam, Here's some code to do it Dim cell As Range For Each cell In Selection cell.Value = TimeSerial(cell.Value \ 100, cell.Value Mod 100, 0) Next cell -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Sam96434 >"...

new / delete in a dll
Hi, I have a dll that allocs some memory with 'new' and returns the pointer to the memory: in dll: char* p = new char[100]; return p; My main application then calls delete on the pointer directly (not via a call to the dll), and this causes a fault. the dll is still loaded, i have not free'd it at this point. It appears that i cannot delete memory that is allocated in the dll. Is this correct? And if so, why?! JoeB wrote: > Hi, > > > I have a dll that allocs some memory with 'new' and returns the pointer > to the memory: > > i...

VBA to open website and pass details
I have a acceess database for a shop and what to take payment via a online terminal, i have the website address and log in detaits. I have got the following code however i alwasy get an error or "Dim iePage As HTMLDocument" Any idea what could be causeing this Private Sub Command0_Click() Dim ieApp As InternetExplorer Dim iePage As HTMLDocument Set ieApp = New InternetExplorer ieApp.Visible = True ieApp.Navigate "https://live.sagepay.com/MySagePay/loginpage.asp" 'wait for page to load Do Until ieApp.ReadyState = READYSTATE_COMPLETE Loop Set iePage = ie...

Need More Columns
We use Quick Books 2003 which has a feature that allows us to save financial reports to Excel with the simple click of a button. One especially useful report allowed us to sort financial data by Class (i.e., Property) into separate columns in Excel. Once into Excel, we could reformat the data easily. Unfortunately, in 2003 we went over 256 "Classes" and can no longer do that report (P & L by Class). Quick Books simply gives an error message saying "Sorry, you've exceeded 256 columns" and refuses to go any further. Is there an Add In that will increase the number o...

Update data to match based on like datasets
I have a bunch of data in a table say: id,myname,grouped 1,joe,123 2,joe,123 3,sam,123 4,pete,456 5,mike,456 6,alan,456 7,sally,098 8,michael,098 9,joseph,098 Records are grouped together conceptually in the single table based on the grouped value. So joe, joe, and sam are one grouping as they all have 123 for their grouped value. I need to run an update query that will set the "myname" value on all matching records to that of the last value. Don't ask why I would want to replace the names in this way, it's just an example. So after done, I would end up with: 1,sam,123...