Deleting Rows Based On Two Criteria?

Apr 20, 2013

I have a very large spreadsheet (>10,000 rows) of data. I did Conditional Formatting based on duplicate values for the serial numbers column (B) and then sorted to "Put Selected Cell Color on top". Next I did a sort by the Last Scan Date column M (Oldest to New). The date/time format appears as follows

I used the following macro to delete rows with duplicate serial numbers but retain the row that has the newest time stamp. When I run my macro it's doing the opposite where it deletes rows with the newest time stamp and retains the oldest time stamp.

Code:

Sub Test()
'for Macro to Delete Duplicate Rows and Retain Unique Value
Dim LR As Long

[Code].....

View 9 Replies


ADVERTISEMENT

Deleting Rows Based On Two Criteria?

Jan 30, 2013

I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)

View 5 Replies View Related

Deleting Rows Based On Criteria

May 29, 2012

I have this excel file where I every day have over 10.000 rows. I have 2 sheets, one called "Data" and once called "Include list".

The Data sheet contains a list of all of our customers and their customer IDs. The Include list sheet should contain the Customer ids which I want to keep in the Data sheet.

So what I have done is to loop through the Data sheet. If you are in the Include list sheet you should not be deleted fromt he Data sheet. If you are not then the row should be deleted.

I have actually done this and it works but the problem is it take a lot of time to run. I tested in earlier today and I had to break it after 20 minutes which is way too long for our users to wait.

This is the code I wrote:

Sub Include()
Dim FindString As String
Dim Rng As Range
Dim RowNr As String
Dim Lookup_x As String

Application.ScreenUpdating = False

[Code] ........

So my question is, is there any other way to speed this process up? The ScreenUpdating part I have tried but it didnt really speed it up as much.

View 1 Replies View Related

Deleting And Adding Rows Based On Date Criteria

May 21, 2006

ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.

View 9 Replies View Related

Deleting Rows With A Criteria

Oct 23, 2009

I've got a file set up like this:

1001 Value 1
Value 2
Value 3
1018 Value 1
Value 2
1023 Value 1
Value 2
Value 3
Value 4
1029 Value 1
Value 2
Etc.

Now I want to delete the sections where the 1000-something value ends on 3/4/7/8 and keep the sections where the 1000-something value ends on 1/2/5/6/9/0 So the result would look like this: ....

View 9 Replies View Related

Deleting Multi Rows If 1 Row Criteria Met

Feb 12, 2010

I have attached a sample of my data. What I am looking for is the best (most elegant solution) to my problem.

I am trying to create a macro where I have 2 sheets. Sheet 1 will contain my data, sheet 2 will contain a single column of criteria to search through sheet 1.

I would like for the macro when it finds that criteria, it will delete all rows of data that is tied to that ORDER_NUM

Schedule

EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2113510395CANADA EMPACT20100108IN445N3113510395CANADA EMPACT20100108IHT9534113510395CANADA EMPACT20100108ID981C5115436953CANADA EMPACT20100110IPMDMN6115436953CANADA EMPACT20100110IN246F7116292017CANADA EMPACT20100111IY1G0M8116411203CANADA EMPACT20100111IT571R9119169642CANADA EMPACT20100113I9M62C10119169642CANADA EMPACT20100113IT272C


For example, it my criteria was part N445N, I would like for it to delete all rows of data that is tied to that ORDER_NUM (113510395).

Desired Output

Schedule

EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2115436953CANADA EMPACT20100110IPMDMN3115436953CANADA EMPACT20100110IN246F4116292017CANADA EMPACT20100111IY1G0M5116411203CANADA EMPACT20100111IT571R6119169642CANADA EMPACT20100113I9M62C7119169642CANADA EMPACT20100113IT272C

View 9 Replies View Related

Deleting Rows That Dosent Match The Criteria

Feb 7, 2008

i have a speadsheet with 2 columns

1 good work
2 good
3 excellent
4 needs improvment

i need a macro that goes through each row and does a search in column B for a partial match of a word. if that word isnt present in the row, the row is deleted.

if i typed in a criteria of "good"
rows 3 and 4 are deleted.

so my speadsheet will then look like this
1 good work
2 good

View 9 Replies View Related

Deleting Columns Based On Certain Criteria

Jul 6, 2014

I am having trouble coming up with an algorithm for deleting columns, based on a certain set of criteria. Heres the issue, all the columns have a "1" the top of column . If that column has a cell number that's greater than or equal to 0.90 or less than or equal to -0.90 then that column gets deleted, if it just has a "1" then the row doesn't get deleted. Very complicated set of criteria.

View 6 Replies View Related

Deleting Workbook Based On Criteria In Active Worksheet?

Aug 16, 2012

I am trying to delete a workbook, yes the whole workbook, if cell A1 is blank.

View 2 Replies View Related

Deleting Rows Based On Their Value

Jun 26, 2008

I am having trouble with the .SpecialCells(xlCellTypeBlanks)

What I need to do is for a macro to first go through a range ("G8:G50"), Add a 0 value to any blank cell.

Then I want the macro to delete the entire row for any of the cells that have a value of 0 in that range of "G8:G50"

View 9 Replies View Related

Deleting Rows Based On Date

Feb 24, 2014

I run a daily report that contains material usage and I copy that into a workbook designed for Data Analysis. I only keep a years worth of usage in the workbook so I need part of my macro to look at todays date and delete anything that is older than a year.

View 4 Replies View Related

Deleting Rows Based On Value Of Cell Within That Row

Nov 14, 2008

I would like to delete rows in a worksheet based on a value of a cell within that row. The worksheet will not always have the same number of rows. I will be looking at the value of each cell in column "AD", which is a date, and if it is not equal to 00/00/0000 then I want to delete that row.

View 7 Replies View Related

Deleting Rows Based On Value In Columns

Apr 28, 2009

I'm sure this is simple code, but...

I have a data table with over 20,000 rows that refreshes each day. I need to run a bit of code when it refreshes that says if the value in column R is 2, then delete that row.

View 14 Replies View Related

Deleting Rows Based On 2 Columns

Oct 9, 2011

I need to delete rows that are blank, 0, or contain errors (#REF, #N/A, etc). My problem is that once in a while, an error will show up in a singe cell of a row that I need to keep so a simple deletion of only errors/blanks/0 will not work for me.

The two cells I need to check for errors, blanks, and 0's are D and E. There can be an #N/A in col D and a 0 in col E or both D and E can have errors. Blanks will occur in all cells of the row.

I have tried to use the code below but it doesn't do anything, but it also doesn't give me an error.

Code:
Sub DeleteAll()
Windows("Template.xls").Activate
Dim r As Long
For r = 2 To 36500
If Range("D" & r).Value = "#N/A" And Range("E" & r).Value = "#N/A" Then
Range("D" & r).EntireRow.Delete = True

[code].....

View 1 Replies View Related

Deleting Rows Based On Set Of Conditions?

Dec 30, 2011

I have the following data

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-DFWCDHJKL3SD RAJESHFirstDFW---
976533060397MAALIT-DFWCDHJKL3SD RAJESHFirstDFW---
976533060397MAADFW-DXBCDHJKL3SD RAJESHFirstDXBUS--
976533060397MAADFW-LITCDHJKL3SD RAJESHFirstLITUS--
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060397MAANBO-DXBCDHJKL3SD RAJESHFirstDXB---
976533060398MAADXB-DFWCDHJKL3SD RAJESHFirstDFW---
976533060398MAALIT-DFWCDHJKL3SD RAJESHFirstDFW---
976533060398MAADFW-DXBCDHJKL3SD RAJESHFirstDXBUS--
976533060398MAADFW-LITCDHJKL3SD RAJESHFirstLITUS--
976533060398MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060398MAANBO-DXBCDHJKL3SD RAJESHFirstDXB---

I need to delete rows based on following conditions, If,

Ticket number, origin, FB Data, Desc, pax name is same i need to delete all rows except the Row which has Destin filled in. In the above example I need rows only with NBO

Result should be as follows,

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060398MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO

I need one more criteria to be tested the above result. IF,

origin, FB Data, Desc, pax name is same and ticket no is A1+1 i need to delete the second row

Final result should be

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO

View 2 Replies View Related

Deleting Rows Based On Cell

Apr 19, 2012

This is the final code needed...

Col G = Product #
Col H = Description

if Col H ( description ) has 'CVP' or "CO2" or 'CKN' = delete row

unless....

Col G is 617367, 858556, or 882949 then keep that row..

View 7 Replies View Related

Deleting Rows Based On A Condition

Aug 8, 2012

I have a data dump. I download everyday with 200 rows and 20 columns. I need to remove any rows where there is no value in either columns C or D. Is there a quick way to do this?

View 7 Replies View Related

Deleting Rows Based On Conditional Formatting

Jun 11, 2009

I'm trying to get Excel to delete entire rows based on the conditional formating i'm using.

Basically, I want to get rid of all the rows that are of a certain color (let's say green, InteriorColor = 4). I've already come up with a way to delete rows based on color, but I have to take into account the Conditional formating i'm using.

Here's what I already have:

View 10 Replies View Related

Deleting Rows Based On If Match Found

Jan 23, 2012

I run a match formula which returns the row numbers of items i need to delete - at the moment, when a match is found, it will say "Match found, delete row 4" for example. Since multiple matches, potentially into the hundreds could be found - is there a way that i could run a script to delete the rows automatically when a match is found? So instead of putting the above "match found, please delete row 4" into an adjacent cell, could we just delete row 4? & then move onto the next row where a match is found?

View 9 Replies View Related

Deleting Rows Based On ListIndex Match

Feb 26, 2008

I need some assistance with the code below. I have a userform that has a listbox with 2 columns of information. When there is a match between the value in the listbox and the value in the cell for a given row, that row is deleted. I thought this was very straightforward, but i'm not getting this to work right...

Private Sub cmdDelete_Click()
Dim Val2 As String
Dim Val3 As String
Dim doit As Long
Dim roww As Long
Dim LastRow As Long
Val2 = ListBox1.List(ListBox1.ListIndex, 0)
Val3 = ListBox1.List(ListBox1.ListIndex, 1)
doit = MsgBox("Confirm that you want to delete the entry", vbOKCancel)
If doit = vbOK Then

View 9 Replies View Related

Deleting Rows Based On Expiry Date

Nov 19, 2008

I have a worksheet. Column A contains employee name, column B contains the date of the first day of the week's holidays they are booked for, i.e. 06/12/08 means they start their holiday for one week starting 06/12/08. That's probably really obvious, sorry.

Once a week I update the vacation list. I want to write a VBA code that looks at the date in Column B and deletes the entire row if it has occured. I'd like to use a value in C1 as the reference so if that cell has 06/15/08 in it then the code would delete every row where the date is prior to 06/15/08.

View 9 Replies View Related

Deleting The Rows Based On A Couple Of Variables

Nov 25, 2008

I have a worksheet ( adage inventory ) and I am trying to clean up some un-needed rows.

I need this deleting of rows based on a couple of variables.

I need to keep any row that Col F has the text "QCCONTROL"
and
I need to keep any row that COL N has the text "HOLD"
and
I need to keep any row that COL N has the text "REJECTED".

Sub Delete_OK_Lots()
lr = Sheets("adage inventory").Cells(Rows.Count, "A").End(xlUp).Row

For x = lr To 2 Step -1
If Sheets("adage inventory").Cells(x, "N") "HOLD" Or Sheets("adage inventory").Cells(x, "N") "REJECTED" Or Sheets("adage inventory").Cells(x, "F") "QCCONTROL" Then
Sheets("adage inventory").Rows(x).EntireRow.Delete
End If
Next x
End Sub

View 9 Replies View Related

Deleting Rows Based On Font Colour

Mar 30, 2009

I have 11 names, one name in each cell, in one row. I want to be able to delete a row if there are more than 3 names in that row that have the same font colour, eg red. Would have to be a loop type macro to check all of the rows on the one worksheet.

View 9 Replies View Related

Deleting Rows Based On Info In Certain Cells

Nov 29, 2009

I have a column that has different SIC codes in it (00045, 00046, etc). I would like to set up a macro or filter that would take cells in this column and delete each row that contains a certain code that I don't want.

Example, I want to delete all the rows with SIC codes that have 00345, 00873, 00145, etc in a particular cell. I would like a macro that would look at each cell in the column and delete the rows that I don't want.

View 9 Replies View Related

Deleting Rows Based On Values In Certain Column

Oct 24, 2006

How can I delete rows in a worksheet using values in a certain column as criteria?
Example: I want to delete all rows that show a value of zero in column k. How is this done?

View 6 Replies View Related

Deleting Rows Based On Columns Data

Jul 2, 2007

I'm trying to write a macro that will look at a table of data, and if column J is blank, it will delete the entire row and shift the data up. Also, if there is a "C" in column C, it will delete that entire row as well. I tried the code below, but for some reason it is not deleting all of the rows that it should. Is there some kind of problem with the code that I'm not catching? It looks like it should work perfectly...

View 9 Replies View Related

Deleting Rows Based On Cell Content

Oct 17, 2007

I wish to remove the page headers that were imported along with the raw data. Here's the code I've been using.

Column_To_Check = 3
Start_Row = 1
End_Row = ActiveSheet. Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count
MsgBox End_Row
Search_String = "."
For Row_Counter = Start_Row To End_Row
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value <> Search_String Then
ActiveSheet.Rows(Row_Counter).Delete
Row_Counter = Row_Counter - 1
End If
Next Row_Counter
End Sub

When I import the data from the text file, there are 3 dimensions separated by a period in the same field. I.E: ABC.DEF.GHI and I use the break points to seperate as such: ABC|.|DEF|.|GHI|. Therefore, all rows that contain data also contain a period in cell C, but headers don't.

The Problem

End_Row = ActiveSheet.Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count

CurrentRegion only selects up to the first blank row. However, I need this to select the whole sheet, not just the current region.

View 9 Replies View Related

Deleting Duplicate Rows Based On Cell Values

Nov 28, 2013

I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag

Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.

And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.

View 1 Replies View Related

Excel 2010 :: Deleting Rows Based On Cell Value

May 21, 2012

I'm trying to delete a range of rows based on the value of a given cell. For example, if cell AC1 shows 5000, then I'd like to delete (not just clear) the range A5000:AA1048576. Or, if cell AC1 shows 10000, then I'd like to delete range A10000:AA1048576. I've come up with what is below but I don't think this is the right way to do it. I am using Excel 2010.

Code:
Range("AC1").Select
ActiveCell.FormulaR1C1 = "=COUNT(C[-27])+2"
Calculate
Range("AC1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A" & Range("AC1").Value:"AA1048576").Select
Selection.Delete

View 5 Replies View Related

Deleting Rows Based On Multiple Filter Options?

Aug 3, 2012

I need to review a 400,000 row spreadsheet and remove all records where 5 columns are populated with data so I am only eft with records that are missing information.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved