Conditional Formula, Then Delete Rows
Jan 22, 2010
In column A, is a timeline - based on every 15 minutes.
In column H, I use conditional formula to change the cell color of any cell equal to or greater than the value of 00:00:07.
I would like to delete all other rows in column H that is under 7 seconds.
I would like the rest of the worksheet to stay in tact - so I can see the timeline. (or at least column A)
I have tried several macros and vba codes, and it either deletes all the rows or simply does not work with my worksheet.
I am unable to attach a worksheet, but I copy/pasted a simple view. This continues down the page, until 24 hours is complete.
Where 00:00:00 is, I am looking for anything over/equal to 00:00:07 - then delete the rest. The cells are formatted as 'general'.
View 9 Replies
ADVERTISEMENT
Sep 18, 2009
In column J of my Excel 2003 worksheet named: Sheet1, is a list of mailing addresses.
I need a macro please to delete the entire row if any of the following conditions occurs:
1. The cell is blank or
2. The cell contains the works: No Address, BUNGENDORE, NSW 1111
Please note that the words: "No Address, BUNGENDORE, NSW 1111" may appear in either Upper-case, Lower-case or Proper-case.
I have attached a sample copy of my spreadsheet and highlighted in yellow the cells that meet the above-mentioned criteria.
As such, the entire rows: 4 and 11 of my sample spreadsheet would need to be deleted.
View 3 Replies
View Related
Jul 26, 2007
I have a worksheet that I use Conditional Formatting to check the dates in a total of 5 columns. In those columns, if the date is before 8/1/2006, it changes the cell color to red. Is there a formula I can put into Condition 2 to that would then clear out the whole row if within those 5 columns there isn't a red cell? This would take hours of work off of my slab.
******** ******************** ************************************************************************>Microsoft Excel - Credetnialing Master.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutAT1=
ATAUAVAWAXAYAZBA1LicenseExpirationInsuranceExpirationDEA_ExpirationStateStateDrugNumberStateDrugExpirationAttestationDateNPDB_Date212/31/20078/2/20071/31/2009*229312/31/20073/7/20073/26/2007312/31/20075/1/20082/28/2010*253812/31/20077/6/20067/24/2006412/31/20078/26/200712/31/2007*256312/31/200712/12/200612/14/2006512/31/200710/19/200712/31/2009*276812/31/200711/28/200412/8/2004612/31/20079/25/20078/31/2007*303012/31/20074/6/20064/17/2006712/31/200712/3/20077/31/2009*315212/31/20075/3/20055/11/2005812/31/20078/2/20078/31/2008*323612/31/20072/28/20073/26/20079**10/31/2009****7/7/20031012/31/20078/10/20073/31/2008*353512/31/20074/11/20064/24/20061112/31/20072/1/20083/31/2009*365012/31/20
.........................
View 9 Replies
View Related
Oct 6, 2011
After red filling certain cells based on some conditional formatting, I apply a filter to hide them. Now I need to delete them using a macro script.
View 9 Replies
View Related
Sep 30, 2008
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
View 9 Replies
View Related
Jun 1, 2009
I have a macro that deletes the active row. However if any cell within rows 1-8 are selected then I want the sub to end with an error message i.e. (Cannot delete these rows) The code I have tried is below but doesn't work:
View 2 Replies
View Related
Apr 29, 2012
I have an inventory sheet with rows containing a formula. I've placed the same formula in all 65536 rows that Excel 2003 has.
I have a macro to delete unused items. It searches for an item number then deletes that row. (Actually it deletes the item number and a partial row based on an offset, but that was for an earlier version and an entire row would be okay) When a row is deleted, all the other rows move up and Excel creates a new last row (65536) The problem is that this new row has no formulas. While it's probably unlikely that I'll ever blow through 65536 rows, it seems sloppy to leave this unaccounted for.
Is there a way to either add the existing formula(s) to the last row...or insert a new row *somewhere* that is empty except for the formula of the other rows in the sheet? Here's what I have for the existing code.
Code:
'Search for item to delete based on entry to InputBox
Sub Delete_Item()
Dim FindString As String
Dim Rng As Range
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[code].....
View 9 Replies
View Related
Jul 22, 2006
I am using the formula =IF(ISNUMBER(SEARCH("v",A2)),"OK", "Not OK") to say if cell a2 contains the letter v, then return ok. It would be really helpfull if I could say : if cell a2 contains the letter "v" or the letter "w" or the letter "x", then return ok.
View 2 Replies
View Related
Sep 11, 2007
After performing several operations and sorting the results I end up with a lot if #VALUE! errors in colum B. I would like to search thru those results and delete any row containg "VALUE! in colum B
View 2 Replies
View Related
Jul 30, 2009
I'm trying to find a way to delete blank rows that contains formula. I have two excel sheets.
sheet1 contains information and sheet2 references the values from sheet1. Let's say I put the following values in sheet1:
ColA
AAA
(blank)
(blank)
BBB
(blank)
CCC
After entering above values in sheet1, here's what sheet2 would look like:
ColA
AAA
(blank that holds formula) =IF(ISBLANK(Sheet1!A2)," ",Sheet1!A2)
(blank that holds formula) =IF(ISBLANK(Sheet1!A3)," ",Sheet1!A3)
BBB
(blank that holds formula) =IF(ISBLANK(Sheet1!A5)," ",Sheet1!A5)
CCC
What I really wanted to happen is for macro to eliminate blank rows in sheet2 to look something like:
ColA
AAA
BBB
CCC
Is it something possible?
View 14 Replies
View Related
Aug 25, 2009
i have the following formula applied to a conditional format, on row 12:
cell value > less than =($K12:Y$111)*(1-0.05)
i want to use this on all rows, however i have 300+ rows.
Is there a way to apply this to all rows between column K and Y, i don't like the thought of creating 300+ conditional formats!
View 14 Replies
View Related
Jan 15, 2014
I have two columns
Column L and Column W
When I input something into Column W, I want it to turn green if it is equal to the corresponding value in Column L
So say we're on Row 50
And I put $280 in Cell W50
I want it to turn green if W50=L50
And red if W50 does not equal L 50
Infinitely in the same column
View 5 Replies
View Related
Jul 11, 2006
On my excell I have a lot of data on sheet 2 that i automatically pull form a database.
I have this fomula in a cell on sheet 1.
= SUMPRODUCT(--( 'Sheet2'!P5:P50000=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
The problem is that when the data on sheet 2 gets updated and more lines added, the computer changes my formula for the cell on sheet 1 as well. the new formula will change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50126=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
when there is 126 lines of new data addes in on Sheet2. Id only 50 lines were added in on sheet 2 then the Formula would change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50050=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
View 9 Replies
View Related
Feb 18, 2007
I have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:
Example:
Row1: LAST, FIRST, data1, data2, data3, etc...
is there a formula which can "check" the larger sheet for duplicate names (a row with exactly the same FIRST and LAST), and then either:
1) delete these rows from the smaller sheet
2) clear the contents of those rows
3) or at least flag them in some way so I can quickly delete them
it would be quite a task to eyeball and remove these rows one-by-one, so i'm wondering if a formula could somehow do it (I don't really know anything about visual basic)
One other piece of information which might be important:
For these rows containing duplicate first & last names between the 2 sheets, the entire row is not a duplicate entry; only the names will match (columns A & B)... The other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything....
View 4 Replies
View Related
Jan 25, 2013
I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
View 6 Replies
View Related
Jan 22, 2008
I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
View 9 Replies
View Related
Jul 15, 2009
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
View 3 Replies
View Related
Aug 21, 2009
I have a ColA that houses Customer Names...
I need to delete any ROW that doesn't have a customer name SOMEWHERE in the ENTIRE Customer Description...
Say this is the COL A, """Campbell Soup/CA (Queensway)"""
if "Campbell" is in row... then DON'T Delete....
i was thinking how the =find() formula gives a number signifing a "find" within a text, but I don't know how to use that in VBA.
View 9 Replies
View Related
Apr 15, 2014
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
View 2 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Apr 16, 2014
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
View 5 Replies
View Related
Apr 3, 2014
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
View 14 Replies
View Related
Sep 2, 2008
Is there a formula possible where by the below table gets the second row deleted
because Col. C has 2nd row with value of 0. This way all the rows with value of 0 in coloum C get deleted and rows move up.
Col.A Col.B Col.C
12 CX2 1
14 CX6 0
16 CX4 5
View 9 Replies
View Related
May 22, 2008
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
View 4 Replies
View Related
May 10, 2012
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
View 1 Replies
View Related
Aug 28, 2007
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
View 2 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
View 2 Replies
View Related
Jun 21, 2008
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies
View Related
Feb 9, 2010
Im having some issues with some code Im working on. My goal is to delete the rows that do not have conditions met. Conditional formatting is used in 6 columns of my spreadsheet and the cell fill is to turn red when below .50 . This code is shown below. What I need to do now is look at the 6 columns where the conditions are applied, and delete any rows where the column cells are not red. To further clarify, if in row 1 column X:X does not meet criteria, but column AL:AL does, the row would not be deleted. However if none of the columns meet the criteria, the entire row is deleted.
View 6 Replies
View Related
