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


ADVERTISEMENT

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

Down Rows Until Specific Value Is Found Within Index / Match Formula

May 14, 2014

My issue concerns going down rows in a data set. Due to the fact that the data set I am pulling from is not standardized, and roughly 10% of the data set has two extra rows of extraneous data below the reference cell (foreign currency balances which I don't need). The point in my formula is the "+2" (down two rows) condition. I needed it so that the "+2" is replaced with a condition inwhich after the reference cell in the INDEX( and MATCH( function is found the command goes down rows until the term "USD" is found. My formula is as follows:

{=IFERROR(INDEX('SHEET1'!$A$9:$O$10000,MATCH((RIGHT(C7,8)),(LEFT('SHEET1'!$A$9:$A$10000,8)),0)+2,11),INDEX('SHEET1'!$A$9:$O$10000,MATCH((RIGHT(C8,8)),(LEFT('SHEET1'!$A$9:$A$10000,8)),0)+2,11))}

View 1 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

Add Rows Based On Found Row On Another Sheet

Dec 17, 2006

I want the code to insert a line under the row in which the code will find the value of the cell from an another sheet (there are two sheets - enter and base). If the code can't find the given value it should go to the last empty row in sheet and then perform some action (probably copy/paste job). Here is the code I came up with, the only thing I don't know is how to set the searched value as the A1 cell value of the sheet "enter" and what procedure to use in case this value is not found.

Sub findme()
'x is the A1 cell value in sheet enter
Workseets("base").Activate
Columns("B:B").Select
'if the following find procedure is successful
Selection.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'then
ActiveCell.Offset(1, 0).Activate .........

View 8 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

Search Column Bottom Up Then Delete Rows Based On Value Found

Jul 7, 2009

I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change.

I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell.

For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.

View 5 Replies View Related

Conditionally Format Rows Based Duplicates Only Found In First Column?

Dec 6, 2013

I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:

A
B
C
D

[Code]....

[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]

I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:

A
B
C
D
E

[Code]....

If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be

View 6 Replies View Related

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 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 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 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 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 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 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 Row If Keyword Not Found

Mar 15, 2009

I would like to have a macro to loop through all the worksheet except for "Summary" worksheet.

The macro will look for the keyword in the worksheet starting from row 2 from every sheet.

if the keyword is found on the row, the entire row will be deleted and shift row up.
Keyword for example is found in H13 in the Summary worksheet

Summary
GH13KeywordLondon

Excel tables to the web >> Excel Jeanie HTML 4

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