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
ADVERTISEMENT
Jun 12, 2014
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 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
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
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
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
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
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
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
Jul 24, 2014
I've been piecing a macro together from different sites and have run into a couple issues I can't find resolutions to. I have a excel doc for tracking paint emissions per week. I would like to have a button on my master tab that will add a row and fill the formatting from the line above across all 60 some sheets. Ideally I would like the same button to first prompt if you want to add or delete rows. When adding rows I would like it to add it above the button (there's 3 buttons and for 3 different sections) and if deleting ask which row to delete. If it can't be done in one button that's fine, I can live with that. The big thing I need is for it to replicate across all of the excel sheets. I've attached a copy of the document
View 1 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
Mar 4, 2014
I need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.
I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.
Here is the source data:
Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT
Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT
View 4 Replies
View Related
Oct 11, 2011
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
View 1 Replies
View Related
Jul 8, 2009
Here is what Im trying to do: I have a VERY large excel file that has groups of 4 cells, a blank, then another 4, repeating. Kinda like the following:
ExampleCell1
ExampleCell2
ExampleCell3
UID = example
ExampleCell1
ExampleCell2
ExampleCell3
ExampleCell1
ExampleCell2
ExampleCell3
UID = example
That pattern continues for about 3000 lines. However, if you noticed, some of the "groups" dont have the 4th line "UID = example".
Here is what Im trying to do. I want excel to search for the cells with "UID" and DELETE it plus the 3 rows above. So that only the "groups" without the UID remain.
Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 3000+ lines of text.
View 7 Replies
View Related
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
May 22, 2009
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data.
I tried using VLOOKUP which is not working as I am having multiple values in column A.
View 9 Replies
View Related
Apr 6, 2013
Is it possible to search for multiple values (and present the result in a certain cell) within a range that spreads over two columns? For example: between A10 to A15 and B1 to B10.
View 6 Replies
View Related
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
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
Oct 29, 2008
I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
View 9 Replies
View Related
Jun 9, 2014
I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error
=IF(ROWS(A$9:A9)>$A$5,"",INDEX(Data!A$2:A$387,SMALL(IF((Data!$A$2:$A$320>=$A$2)*(Data!$A$2:$A$320<=$B$2)*(Data!$B$2:$B$320=$C$2),ROW(Data!$A$2:$A$334)-ROW(Data!$A$2)+1),ROWS(A$9:A9))))
test1.xlsx
View 3 Replies
View Related
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
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
Sep 24, 2010
I have a workbook that contains 18 sheets of data. These worksheets are named according to their respective client names (AAA Shine, Fern Barn, Oracle, etc. for example). On each of these sheets, is a column (Column B), that has a salesperson's name in it (Fred, Joe, Ted, Anne... there are about 10 names total). On each sheet, there are corresponding values for the revenue from that account for each month of the year. Those values appear in Column L (Jan), M(Feb), N(Mar),... W(Dec).
What I would like to have is formula that will look at each sheet, find all the instances of Fred for January, sum them, and report them in a single cell on a different sheet I have called "Summary". The end result will be a total revenue from all customers, for each sales person, by month, in one summary sheet.
View 9 Replies
View Related
Aug 21, 2006
As in attached file, I have 5 tables:
Table 1: matrix that contains Weight data in each cell
Table 2: matrix that contains Code data in each cell
Table 3: Relationship between Code and Level data
Table 4: Table with relationship between Weight data, Code data and Level data
Table 5: I want to populate each cell in Table 5 matrix from getting values from Table 4 by looking up the right value from Table 5 based on :
1) Weight data from corresponding cell in Table 1
2) Code data from corresponding cell in Table 2
3) Code and Level relationship.
for e.g. to get value for Table 5, Worksheet cell B140 ( intersection of A1-A1),
1) corresponding weight is 1.17 as in Table 1 (Weight data) (Worksheet cell B3)
2) corresponding Code is 6 as in Table 2 (Code data) (Worksheet cell B35)
3) Code 6 corresponds to Level 2 (as seen in Table 3)(Worksheet cell A74)
so, looking up the 3 values from Table 4 i.e. Level 2, Code 6, Weight 1.17, one gets cell AR98 with corresponding value. therefore, final value in Table 5, Worksheet cell B140 (intersection of A1-A1) will be equal to $4.00
I have been trying to solve this using Vlookup, Hlookup, Index/Match but am getting confused. Also, not too sure if this can be done in 1 step. New to these excel and these functions
View 2 Replies
View Related
May 20, 2008
I have three parameters that users on my spreadsheet will be using. They will be searching using the 'Brand', 'MPAN' and 'Tariff' fields.I would like to have drop down menu's for then to select the correct search option. I then have another sheet within the same workbook with all the data in it with columns headed of Brand', 'MPAN' and 'Tariff'. It has to be an excat match using those three parameters though. I would like the parameters the user selects on the first sheet to be searched and found on the second sheet, with the figures in the cells next to the search result getting fed back into a set cells back on the first page.
View 9 Replies
View Related
Apr 11, 2014
vba script to convert the text in a cell(split considering "Space" as delimiter) into multiple Rows and Single column & the resultant row's(A1:A7000) values need to be searched in Column(B) of another sheet,if the search result is false then the value in the row need to be highlighted in red.
I am able to split the values in one column into mutiple rows,but need to have all the resultant values in a single column.
i,e,.
I am able to split it as below:
Column A
Column B
Column C
[Code]....
for second situation - search I could search based on the values by using Vlookup() function,but unable to highlight when search result is false.
ex:
if the search string "ACB" in Sheet1 is not available in the sheet2, then value "ACB" should be changed into red.
View 1 Replies
View Related
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
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
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