Macro To Delete Rows That Have Cells That Don't Contain Values From A List

Mar 7, 2014

Each day I am going to have a list of about 300 different ID numbers, which i have already got a macro for creating, that outputs them into a single column on a sheet.

The next challenge Ill face now, is that I'll have a list of maybe 500 ID numbers in another spreadsheet.... I need a way to basically tell excel to keep rows that contain the numbers in 1 column on the list of 500, that correspond with those on the list of 300.

Numbers that are not found on the list of 300 ID numbers, must be deleted, along with the entire row.

I essentially need a macro that runs something like this that i found online, but instead of it just looking for the word "apple" as rows to delete, i would need it to check to see if the number is one of the 300 on my list.... and delete the row if it is not on the list

PHP Code: 

Sub Delete_Rows()Dim rng As Range, cell As Range, del As RangeSet rng = Intersect(Range("A1:C20"), 
ActiveSheet.UsedRange)For Each cell In rngIf (cell.Value) = "Apple" _ThenIf del Is Nothing ThenSet del = cell
Else: Set del = Union(del, cell)End IfEnd IfNext cellOn Error Resume Nextdel.EntireRow.DeleteEnd Sub 

View 3 Replies


ADVERTISEMENT

Macro: Delete Rows Containing Values

Sep 4, 2008

Macro to delete a row if it contains a particular value in a column. Say for example I have the following:

a b c d
1 x
2 x
3
4 x

I want to be able to put into my macro code that says delete all rows that contain an X in column B. So this would leave just Row 3 remaining.

I could also sort this data so that I get:
a b c d
1 x
2 x
3 x
4

That way I just have to delete all the rows less than and equal to 3. Ultimately I'm trying to get a way for the macro to copy the rows that DONT have a value in a particular column. But I figured it might be easier to find a macro which would delete.

View 9 Replies View Related

Macro To Delete Rows With A Range For Values

Mar 28, 2009

I need some assistance with a simple macro. Im clueless when it comes to macros.

Heres what I have:

column "e" contains the year built for homes. Some of the fields contain "9999" or "0000" or blank. I need to delete these entire rows. If the year is between 1900 and 2020 I would consider it valid and keep the row.

So I need a macro that says something like this:

if column "e" is not between 1900 and 2020 delete the entire row.

View 11 Replies View Related

Macro To Delete All Rows Containing Values Less Than Or Equal 300 In Column

Jun 11, 2014

Need a Macro to delete all rows containing values less than or equal 300 in column A

View 5 Replies View Related

Macro To Search Multiple Values And Delete Rows.

Nov 5, 2008

How to create a macro to search a value and delete the row but I'm having trouble trying to get this to search multiple values. Keep in mind I'm really new at attempting to create a macro so this may look terrible ;-)

I have a list of 20 numbers (changes from time to time) and I need to filter any row containing any one of these numbers out of my results each day. I am currently able to filter a single group of numbers but get an end error every time I attempt to string a group of numbers.

This is what I have so far and is an example. We'll use three numbers as an example.

"12345","12346","12347" are the numbers that we'll use for the example that I need to filter. The code that I have is:

View 13 Replies View Related

Excel 2010 :: Delete Duplicate Rows Based On Values Of Cells

Jun 17, 2014

I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.

A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

or

A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

View 5 Replies View Related

Macro To Delete Rows In Which All Cells Contain Same Or Single Value?

Sep 3, 2013

I need a macro to delete each row in which all cells contain the same value, always starting in cell G2 and going out an indefinite number of cell's. So, for one run, the range could be G2:BU2 and another it could be G2:PW2. Also, the number of row's is indefinite.

And an added complexity: I need it to ignore the cell in this search process if the cell contains "NC" - so if all the data in the row is the same except for a few that say NC - then it gets deleted. If the any of the cells in the row have other values, then that row does not get deleted - even if it contains "NC".

View 2 Replies View Related

Macro To Delete Rows Where There Are Blank Cells

Sep 25, 2013

I would like code that will delete all rows where there are blank cells in Col D from row2 onwards.

View 2 Replies View Related

Macro To Delete Entire Rows If Certain Cells Are Empty

Mar 5, 2009

I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.

So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.

I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.

View 14 Replies View Related

Excel 2010 :: Can't Delete Filtered / Visible List Rows From List Object

May 19, 2014

I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.

View 5 Replies View Related

Delete Rows Based On Values In Rows Below

Jun 20, 2008

I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.

View 2 Replies View Related

Delete Rows From Filtered List?

May 11, 2012

I can see that I'm not allowed to delete rows from a list when it's filtered. Do I have any options to avoid this restriction?

View 2 Replies View Related

List Of Values - Delete Lines

Apr 17, 2014

I have a list of values. they are in C2:C25

I want to go dorn column B, starting at B26:B, and if the value in B equals any of the values in C2:C25 than delete that entire row(not just delete the word, because there are values in column C and D that I need gone with the C value).

View 5 Replies View Related

Delete Certain Characters From List Of Values?

Mar 13, 2013

I ve got a list multuple values. All of them contain 00 before the actual value. How can I delete those two 00.

Here's an example

Currently Desired Outcome
0030148099 30148099

0030148099 30148099

0030148100 30148100

0030148100 30148100

0030148101 30148101

View 2 Replies View Related

How To Delete Visible Rows In A Filtered List

Jan 30, 2013

how to delete only visible rows in a filtered list?

View 9 Replies View Related

VBA - Delete Rows With No Values

Oct 18, 2011

Delete columns with no values in them:

Code:
Sub DeleteAllBlankColumns()
Dim LastRow As Long
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
On Error Resume Next

[Code] .........

I want it to ALSO delete entire rows in the worksheet that do not have any values across the entire row. Currently the worksheet prints rows that have borders but no values. This would eliminate that problem.

But if there is a value in any cell of the entire row it keeps that row.

View 8 Replies View Related

How To Delete Rows With Values That END With .1 Or .3

Nov 27, 2013

I have a long list of IP addresses in an excel column, they all either end with .1 or .3, i want to be able to get rid of any ip address in the list that ends with .1 but when i select the column and choose filter by ends with and put the value (i have tried .1, 1 *1 ".1" and "1") and all it does is remove every value, the column looks like this

10.10.5.1
10.10.5.3
10.10.6.1
10.10.7.1
10.10.7.3
10.10.8.1
10.10.8.3
10.10.9.1
10.10.10.1
10.10.10.3
10.10.11.1
10.10.12.3

and i want to remove all cells that have an IP address that ends in .1 so that it would look like this:

10.10.5.3
10.10.7.3
10.10.8.3
10.10.10.3
10.10.12.3

i figured the ends with filter would work but i dont know why its not doing what i expected it to, there are several thousand cells and it will be impossible to delete them one by one, how i can just get rid of any cell that ends with .1

View 7 Replies View Related

Macro To Delete If Set Of Multiple Rows Equals Another Set Of Rows

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

Delete Rows If Adjacent Cell Value Is From A Defined List

Mar 25, 2013

If I have a list in sheet1, of say 300 names, I need some code, that for a table on sheet2, from A1 to F500 will delete the contents of cells in columns B C and D, IF the value for column A in that row is from the list in sheet1

List of names: sheet1, A1 to A300

Table that needs changing: sheet2, A1:F500

cells to be deleted in columns B C D IF value in A corresponds to the list in Sheet1

View 3 Replies View Related

Use Values From One List To Remove Rows From Another List

Feb 24, 2007

I have two lists in the same workbook:

List 1) Contains customer contact information, including an account number. These account numbers may be duplicated in the list.

List 2) Contains account numbers of customers who wish to be removed from the first list.

I need to remove the rows from the customers list (List 2) where the account numbers match, and also copy those to another list for review. So far, I can manually choose and run some code to remove one particular account number only (eg 123):

Set FoundCell = Range("A:A"). Find(What:="123")
'Locate information to remove
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Copy
Sheets(" Deleted List").Select

'ActiveSheet.Next.Select
Range("A1").Select

Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select..................

View 3 Replies View Related

Delete Rows With Duplicate Values

May 1, 2006

I have a spreadsheet with three colums of data. The first column contains
records which have occasional phone number duplication- see blelow:

(555) 000-0000DataA1ValueA1
(555) 000-0000DataA2ValueA2
(555) 555-9770DataA3ValueA3
(555) 555-4464DataA4ValueA4
(555) 555-4464DataA5ValueA5
(555) 555-4720DataA6ValueA6
(555) 555-8823DataA7ValueA7
(555) 555-3834DataA8ValueA8
(555) 555-4125DataA9ValueA9

What I need to do is (somehwhat) automate the process of filtering or
deleting out all rows which have duplicate data in the first column, but not
second or third columns. I'm sure it's been done...I tried the Excel
out-of-the-box.

View 14 Replies View Related

VBA Delete Rows In Between Cell Values

Oct 13, 2012

Within one column, there are cells with values A, B, and C for example. I want a loop that
- delete all rows from the first row to the row where A occurs,
- delete all rows BETWEEN where B and A occurs.
- and then finally, delete the single rows where C occurs.

This might seem I'm just deleting everything but obviously there are more "A" and "B"s in the column.

1
2
3 A
4
5 B
6
7 A
8 B
9 C
10

So in this example, rows 1 to 3 are deleted since I want to delete row 1 to the row where A is; rows 5 to 7 are gone (B to A); then finally row 8 is gone since C is there. Whether it's A/B inclusive doesn't matter..I just would like to see the (simple) logic.

View 9 Replies View Related

Delete Rows Based On Values

Nov 10, 2008

I have a 20,000 row text file that I import. It has blank rows, page headers and footers, and many rows at the bottom that I do not want. I have been cleaning up the data manually but this takes a great deal of time and I have a need to perform this clean up on a more frequent basis.

I have tried recording a macro to do some of the deleting but I am running into an issue that I do not know how to over come. When I filtered the data based on "Blanks" on a specific column and then try to delete them in mass, Excel errors out stating that my request was too complex.

View 11 Replies View Related

Delete Rows Between Two Cell Values

Jan 28, 2010

I have rows between the Words "vendor" and "sales order" both words are in column A. The number of rows is never constant but needs to loop through the whole document. Is there a way to make a macro to do this?

View 9 Replies View Related

Delete Rows: Macro Skips Rows

Nov 5, 2006

Need to solve my problem in the thread "Type Mismatch Error Message". Now a new problem has come up in the same code, so - according to the rules - I've started a new thread. (This one is most likely due to my poor knowledge of VBA syntax).

Sub Delete_invalid_rows()
Dim i%, j%
Dim Nr%, valid As Boolean, BYPdata As Boolean
Dim ar1 As Variant
Dim ar2 As Variant
Dim ar3 As Variant
Dim ar4 As Variant
Nr = 20
ar1 = Array(11, 14, 19, _
20, 22, 25, 26, 27, 28, 29, _
30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, _ .................

View 2 Replies View Related

VBA / Delete All Rows That Contain Certain Values In A Specific Column

Mar 8, 2012

Is there a VBA code I can use that will delete all rows that contain "ABC" or "YXZ" or "HHH" in column A ?

View 2 Replies View Related

Delete Rows With Values Between Numeric Range

Apr 16, 2008

I want to delete all the rows with values between -1 and 1 in column 'I'. I currently set up a macro to do the formatting and conditional stuff that highlights all values above 1 and below -1 - these are the values I need to conduct my analysis on, but I can't figure out how to delete the rows in between. The worksheet has approximately 5,000 rows (and growing) and this would help me clean up the data significantly.

View 7 Replies View Related

Delete Rows That Contain Data From User Defined Dynamic List Within Workbook

Oct 7, 2012

I have a financial dataset which I need to "clean" before manipulating/analysing.

Each row of the data represents a completed transaction and the first step is to delete rows that are done with particular (internal) clients; the client data (the client's name) is in Column D.

Currently, I delete the unwanted data by autofilter and delete (code below), however this means the clients to be deleted are only defined within the code. I would prefer to have a worksheet within the workbook where the user defines the clients by adding or subtracting their name from a list.

I have created a dynamic range for the client list by inserting a Named variable along the lines of "=OFFSET(!$B$3,1,0,COUNTA($B$3:$B$200)-1," which works fine.

However how do I work this list into my code so that it works when new clients are added or deleted?

Current code for deleting unwanted client data:

Sub filterdelete()

Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count

With Range(Range("D1"), Range("D" & LastRow))

[Code] .....

View 2 Replies View Related

Compare 2 Columns Delete Rows With Matching Values

Feb 5, 2009

I've got two workbooks, Workbook1 with a list in column a and Workbook2 with a list in column F. I want to compare the cells in these columns and delete the entire row in Workbook2 if there is a match.

View 3 Replies View Related

Delete Cells If Found In Another List

Feb 5, 2008

I have a list of email addresses in column A of a sheet1, and a list of "Do Not Send" email addresses in column A of sheet2. I'd like to have a macro that would delete any cells in column A sheet1 that were listed anywhere in column A of sheet2.

View 5 Replies View Related







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