Delete Entire Rows Where 1 Column Meets Multiple Criteria
Sep 17, 2007
I need create a macro to perform the following - I don't know if it needs to be an Excel macro or via VB. This is to speed a process up and eliminate any spreadsheet messing around for our "admin" staff - they are not good with excel at all!
So, I have a daily/weekly down load of a spreadsheet (exported from access).
* I want to copy the values of column AK to A
* Then delete all entries in column D that equal 19, 20 and 6
* Then delete all entries in column AL that equal "A","B","C" or "D" but not "E","F" or "G"
* Then delete entire rows where the value in column A is a duplicate within the list
I will call this spreadsheet, for the purpose of this exercise, the "Master". I now have another spreadsheet with values in that should relate to values in either (or both) columns A and B in the "Master". I need Spreadsheet 2 values to be highlighted as cross checked in the "Master" and then all those in the "Master" that do not appear in the 2nd S/S will be normal white cells thus showing that they need to be chased by our Admin staff.
View 6 Replies
ADVERTISEMENT
Sep 29, 2011
Need to create a macro?
Delete rows where multiple column meets multiple criteria.
detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)
Those 2 criteria have to occur at the same time..
View 1 Replies
View Related
Aug 26, 2013
I am trying to create a macro to accomplish the following task. Delete if the value belongs to queue "ab cd ef" and is also a part of category "xxx xxx".
I wrote the following code, but it seems it is doing some mistake:
Code:
LastRow = Range("Z2").End(xlDown).Row
For i = 2 To LastRow
tempQueue = Range("C" & i).Value
tempRange = Range("Z" & i).Value
If (tempQueue = "ab cd ef" And tempRange = "0 - 1 day") Then
Range("C" & i).EntireRow.Delete
[Code] ........
The above code works fine but it deletes only 120 rows out of 200 or sometimes 150 out of 200. Can't get it to work all 200 rows.
View 4 Replies
View Related
Jun 16, 2014
Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.
elevation
type
grade
percent
weight
5000
5000
5000
5020
[Code] ..........
View 9 Replies
View Related
Nov 21, 2007
Excel Range A1 - D500. Data located in column C. I would like to delete the rows in this spread sheet that do not contain any of the following 3 prefixes of part numbers from column C: X-SXP, X-SCM and X-SBP. There are many part numbers but only parts with these 3 prefixes are needed. Part numbers are 10 characters long after the prefix. If any additional information is needed, let me know.
I viewed the Delete Rows Meeting Conditions thread, but the input value in that code is for what you want to delete, and I need to delete everything not meeting the data I want to keep, which is about 30 different part numbers. Also, that appeared to only offer one input and not 3.
View 3 Replies
View Related
Oct 19, 2011
Simple code that can delete entire row if certain criteria is met in a single cell
Example
I have a bank statement where under a first column (DATA TYPE), the cell could contain either "DATA" or "TOTAL"
How can I delete the rows contain the word "TOTAL" assuming the column is already sorted.
View 3 Replies
View Related
Feb 18, 2014
I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):
Code:
Range("X2:X2000").Formula = "=IF(IF(ISBLANK(W2),,$AD$1)=0,"""",IF(ISBLANK(W2),,$AD$1))"
Is there a way I can amend it so it only updates those cells which currently have no data?
At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.
View 3 Replies
View Related
May 21, 2008
I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".
Sub DeleteFirstRowInWorksheet()
Dim SheetName As Worksheet
Dim i As Integer
For Each SheetName In Sheets
If Range("A1") = "table" Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If
Next SheetName
End Sub
It delete only in an active sheet. What's wrong?
View 5 Replies
View Related
May 4, 2014
i want to delete entire blank row from column C2:C300 i mean if i have data c2:c100 then c101:c300 delete entire blank rows
View 4 Replies
View Related
Jul 8, 2014
In theory I know what I should do, but I don't know the syntax. So here it is:
There are 1450 unique records in my XLS, every record contains 45 different rows(these are the phases) with their position(1....45). Every row has a status (Not Started, In Progress, Complete)
COLA(uniqueid)....COLi(it is a number, it is the position).....COLN(status)
id1....1......status
id1....2......status
id1....3......status
.
. .
. .
id1 ....45.....status
id2.....1.....status
.
.
.id(n)
basically I would like to get the last "in progress", If not found, the last "Complete, If not found then the first "Not Started". and put a "Y" right next to the row to a new column for all the groups(45 rows)
View 12 Replies
View Related
Jun 24, 2009
I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
View 4 Replies
View Related
Feb 7, 2009
here is a sample of the data
150
21
352
422
53
69
755
852
1224
13268
119
242
30
444
543
67.................
I know if I use dmax for only where first column equals 13 I get 460 but how do I get the second highest value for only those rows that have 13 in the first column (expect the answer to be 268). Then I want to do the same for 3rd, 4th highest etc.
I know large does it for one column and not only when the first column matches a designated criteria.
View 3 Replies
View Related
Aug 13, 2012
Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.
Attached sample file : example.xls
View 3 Replies
View Related
Sep 8, 2009
i have several student ID results that have conditional formatting in place. I'd like to KEEP the following data but delete/hide everything else:
1) Filter out purple rows
2) WITH filtered rows: KEEP any scoreA that's 100% or less than 85%
3) Look at Score A and Score B - KEEP if the difference is greater/less than 5%
4) If a student took testB but didnt take testA, KEEP.
View 2 Replies
View Related
Aug 12, 2008
Is it posssible to use the xirr function with conditions. For example, an array formula that will only calculate xirr if a column meets a certain criteria or if the dates are within a range?
View 9 Replies
View Related
Feb 23, 2011
how they can be used to delete rows that meet multiple criteria, however all the examples I have found seem to be rather complex and personal to the individuals as opposed to being rather generic. Many people, including myself would find it useful to have a simplified generic answer that can be applied to all kinds of criteria. Such as if the data in column B = x and the data in column C = y delete the row. Here is my example.
SCENARIO EXAMPLE:
In column A - Date e.g. 05/02/2011, 06/02/2011
In column B - Day
In column C - Time (in format 09:06:21)
In column D – Service numbers dialled (70, 110, 224) two to three digits long
MACRO EXAMPLE:
Delete the row if the criteria in:
Column D = 10, 40, 192 or 244
And
Column C is between 09:00:00 and 16:00:00
macro code or at least a link to a thread that they think gives a scenario as simple as this.
View 5 Replies
View Related
Dec 23, 2009
I am trying to use the code below to eliminate data in a spreadsheet based on certain criteria. I have been able to get this to work with text fields just fine, but now I am running into an issue with numbers. I am trying to get this code to delete rows if they have the word "unknown" in column C and if the value in column B is greater than 900 hours (999:00:00), the numbers in this column are formatted as [hhh]:mm:ss. What should I tweak to make this work?
View 7 Replies
View Related
Feb 29, 2008
I have an extremely large CSV file that I am opening via Excel. Can anyone tell me how to delete a row using multiple criteria. For Example, i want to delete the whole row if a certain cell doesnt equal GA SC or NC. I have seen how it can be done using one criteria but not multiple ones.
View 8 Replies
View Related
Apr 30, 2012
Using Excel 2003.
In column Y, I have a list of employee numbers (there are a random number of blank spaces in between each number)
In column AE, I have a list of clock in times (there are a random number of blank spaces in between each clock in time)
In column AY, I have a list of the same numbers from column Y (there are no blanks in this list)
Column BB. I am looking for a formula to place in cell BB2 and copy/paste down that will match the number in column Y and return the first non blank cell from that point in column AE.
Example:
10062 is employee number in cell AY2. Her employee number (10062) is also located in cell Y5. Her clock in time is located in cell AE7.
10099 is employee number in cell AY3. Her employee number (10099) is also located in cell Y14. Her clock in time is located in cell AE16.
What formula can I place in BB2 so that it returns the clock in time of employee 10062. Then copy and paste so that BB3 returns the clock in time of employee 10099?
View 6 Replies
View Related
Jul 10, 2014
A macro is required to identify rows within a selection e.g. entire column A, that share the same value, then delete appropriate rows depending on the values in another column. The attached example details the requirements.
Extract Rec1.xlsx
View 6 Replies
View Related
Apr 9, 2009
I need a macro to delete old data from a large selection of data, in order to keep the size down.
What I want this macro to do is
Check all rows from 5 downwards.
If A5 (date) is less than cell $B$1 AND B5 is not equal to C5 then delete the whole row.
Continue until reaching the bottom.
View 9 Replies
View Related
Aug 30, 2007
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted.
The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
View 8 Replies
View Related
Feb 29, 2008
I have a Macro that I use daily on a spreadsheet I get with raw line-item transaction data on it. Basically, it deletes columns I don't need to look at, applies column headers, deletes certain rows that I don't need to look at, applies an auto-filter and filters to the first group of data I need to look at, and puts a subtotal on a couple of columns at the top so I can see totals on filtered results.
I need an additional function in this Macro. At some point in the middle of the actions above, I need to evaluate all rows and delete those that are 'opposites' based on a couple of criteria. Basically, I need to delete any transactions that have been refunded in their entirety, because for my purposes those are zeroed out and I don't want to see them....
View 4 Replies
View Related
Apr 4, 2008
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
View 3 Replies
View Related
Jun 4, 2009
I have a large number of user inputs which is linked to a data sheet. In this sheet are rows to be kept and rows to be deleted. See attached example...
I need code to delete all other similar rows than the one with a number (1) in the field Complete. There can only be a number (1) in the field Complete in one of the rows.
But if there is no number (1) in the field Complete in any of the rows, no rows should be deleted.
The data sheet is to be transferred to Access afterwards.
View 3 Replies
View Related
Aug 11, 2008
I have searched for this topic, and I have read http://www.ozgrid.com/VBA/row-delete-criteria.htm, I don't really understand it though. I think what I want is simple, I want to search column A for cells containing the word " Total" - if any are found I then want to delete the corresponding rows.
View 2 Replies
View Related
Apr 21, 2007
I have some VB code, courtesy of OzGrid and Davc4, that works well to delete duplicate rows based on criteria in Column A of the active worksheet (albeit a bit slow on large files).
How do I modify the code below to evaluate duplicate data in Columns A through D? .....
View 7 Replies
View Related
Jan 13, 2009
I have a spreadsheet that has thousands of rows of data with maybe 6 columns.
I would like to remove the duplicate rows but the problem I am facing is that I would like to use column A as the criteria.
There are some duplicates in column A but the issue is that the data in column B, C, D, etc may not be the same for each duplicate.
Easier to see in this image:
http://img127.imageshack.us/img127/6364/datajv6.jpg
So A1 is the same as A6
A1 has data in B1 and C1, no data in D1
A6 has data in B1, C1 and D1
I need to delete either row A1 or row A6, it is irrelevant which row it is but one of them has to go.
View 2 Replies
View Related
Aug 19, 2008
I have the following code to delete entire rows where column IV contains "x".
With ThisWorkbook.Sheets("Sheet3")
With .Range("IT1", .Range("IV" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="x"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End With
End Sub
[/Code ]
For a reason that I can't see, it is deleting not only the row that contains the "x" (there is never more than one row with an "x" in column IV), but also the row immediately below it.
View 9 Replies
View Related
Jun 6, 2007
I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:
1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.
2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.
The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....
View 6 Replies
View Related
