Macro To Delete Rows Based On Partial Text

Apr 9, 2009

I am looking for a macro to delete entire rows based on the a partial text contained in column A. For example, I have a list of names that I need to keep but I need to delete the rows in which column A contain cells starting with the following.

BOOKLET 201, PROMO
LOTTO, PRIZE 2
PBT 2, STW WEEK 4

the numbrs in these cells increment therefore I can not make it a specific to the cell but need to select based on Booklet, Lotto and PBT.

View 5 Replies


ADVERTISEMENT

Delete Rows Based On Partial Match

Aug 11, 2007

I currently have a speadsheet that looks similar to the following:

REF DATA1 DATA2
SVRW_67 784598 475395u
SVRW_34 fdsjsjdf 734978
CAT_56 ghdrhad gaghadh
CAT_67 48578 8943539

I'm trying to create a macro that will delete all rows with "CAT" in the REF column.
I've searched this site and struggled trying to adapt other methods listed and got nowhere.

View 3 Replies View Related

Delete Columns / Rows By Partial Match

Feb 19, 2008

i would like to find code for a macro(s) to delete selected columns and delete row by partial string..

due to my novice excel skills i would prefer in two separate macros so i can trigger from another macro if poss..

View 8 Replies View Related

How To Delete A Row Based On Partial Match In Column

Jul 19, 2013

I am using VBA

I have an input box pop up to ask the user for a base item number. The way the excel sheets part numbers work for example is TM-T88VP-GRY, the last part 'GRY' changing based on the variation of the item that someone ordered. So, the base item number would be TM-T88VP, and what I want to do is search through a set column (Column G) and delete any row that contains that base item number.

View 4 Replies View Related

Delete Row Based On Partial String Criteria

Dec 10, 2009

I have 2 columns A and B. The data is in column B.

What I'm trying to do is delete entire rows from my current selection if they do not begin with mailto:

View 9 Replies View Related

Excel 2007 :: Match Partial Text When Partial Text Is Not Exact

May 20, 2014

This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:

H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP

if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.

So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP

View 11 Replies View Related

Delete Rows Based On A Text Criteria

Aug 24, 2009

I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.

Can anyone help me out in writing the Visual Basic code?

View 8 Replies View Related

Macro To Delete Rows Containing Certain Text

Jan 15, 2009

I have put together a macro that will delete rows (called DeleteUnwantedRows) in a spreadsheet that contain the phrases "no further action" and "not applicable" ... However the macro does not appear to be working.

View 2 Replies View Related

Use A Macro To Delete Rows With Text

Jan 24, 2008

how to use a macro to delete rows that have text in them. I have an imported text file that has headings every 10 rows and I need to take out the heading rows

View 9 Replies View Related

Macro To Delete Rows Based On Alignment?

Mar 4, 2012

I need a macro to identify the word "(blank)" based upon alignment (here it is left most) and to delete the next 4 rows only.

View 6 Replies View Related

Excel 2007 :: VBA Code To Delete Rows Based On Text Starting Content

Oct 25, 2013

Using excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"

EX:

05S0128
06S0112
05S2298
S25852
S36963
SA36185

I would only like to keep the last 3 lines.

View 3 Replies View Related

Select Based On Partial String Then Delete Based On Full String?

Jun 13, 2013

Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.

Here is the format of the report at this point. Names have been changed to protect the innocent.

Agent: 2366 Bacon, Kevin

Date
Start
End

[Code].....

So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.

I know what I want to do...but not shure on the code cause I am not great at VBA.

Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.

View 6 Replies View Related

Macro To Delete Rows Based On Multiple Criteria?

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

Macro To Delete Empty Rows Based On Row Height?

Feb 13, 2014

I'm trying to create a macro to delete all empty rows but only if they are a certain row height. I currently have a macro that looks to the first cell and if it's empty, then it deletes the entire row. The only problem is that I would like to keep the shorter, empty rows to maintain my desired formatting. I will post the code I have if I can figure out how to do it properly and not violate the forum rules.

[code]
Sub DeleteEmptyRows()

'Cell A above and below each header contains white and gray text to maintain formatting when deleting empty rows'

Dim i As Long, LastRow As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row[code]......

View 1 Replies View Related

Macro To Delete Rows Based On Multiple Criteria

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

Macro For Partial String In Text

Oct 21, 2008

I want to write an macro where it searches for text in a column, but the text may be a partial string. The text is in column B, with account numbers in column A, like this:

Column A Column B
1100 Jay
1101 Jack
1102 Jackson
1103 Jacksony
1104 Jefferson

For example, I want to search on Jack in column B. In the above example, I want it to find accounts 1101, 1102, and 1103, and to copy that information to another spreadsheet, say, sheet2.

If possible, I'd like it to copy the first account number and name, 1101 Jack, and if that's not what the user wants, they click next and they see 102 Jackson and so forth.

View 9 Replies View Related

How To Apply Categories Based On Partial Text Match Like Vlookup

Jan 17, 2013

I do PPC work and you can export query data from Adwords with Column A being the actual search query. Then column B - E are impressions, clicks, cost & conversions (from each query). In this example, cell A1 might be running shoes, cell A2 might be walking shoes, cell A3 is running pants, and cell A4 is baseball pants. I want to add a new column (F) that does product category groupings based on partial text matches in column A. For example, anything that includes the text string shoe or shoes should be labeled as shoes in column F. Similarly, all queries that contain the characters pant would be listed as product category pants in column F.

Currently I do this manually by conditional formatting on column A for contains text and then color the cells. Then I sort by cell colors. Then I manually type in the product category into F for each color block in A. Needless to say, this is slow and manual.

What I would prefer is to have a master table (like a vlookup) on sheet 2 where column A is the list of partial text matches and column B is the product category to be returned if that the partial text in sheet 2 column A is found in the query list on sheet 1 column A. This way, on sheet 2 I could have cell A1 sneaker, cell A2 shoe, cell B1 shoes, cell B2 shoes, etc to manage the correlation between text strings and product category groupings.

Basically I want a formula to put in the cells on sheet 1 in column F that searches all of column A on sheet 1, looking for partial text matches from column A on sheet 2 and returning corresponding product category in column B on sheet 2.

View 6 Replies View Related

Excel 2003 :: Calculate Sum Based On Partial Text Match And Latest Date

Jun 19, 2014

I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.

A B C
7/1/2012 0:00825-CL-A-41091-REG4.00
7/1/2012 0:00825-CL-A-41091-REG6.25
7/1/2013 0:00825-CL-A-41456-REG4.25
7/1/2013 0:00825-CL-A-41456-REG6.50
1/1/2014 0:00825-CL-A-41640-REG4.25
1/1/2014 0:00825-CL-A-41640-REG6.50
3/1/2014 0:00825-CL-E-41699-REG3.00
3/1/2014 0:00825-CL-E-41699-REG4.00

View 14 Replies View Related

Macro That Searches For Partial Match Then Apply IF Statement Based On Match

Sep 21, 2013

I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.

I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet

This is what happens if working right =

look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)

Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)

Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).

View 1 Replies View Related

Delete Rows Based On Criteria & X Rows Below

Mar 5, 2008

This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete

End If
Next Row_Counter
End Sub

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

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

Macro That Will Highlight Cell And Four Rows To Right Based On Text In Cell

Aug 6, 2014

I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.

I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.

It would basically say :

If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).

View 10 Replies View Related

Delete Rows With No Text?

Oct 25, 2013

I'm trying to write a line of code in my VBA macro that goes through all of column B and if there is text in the column to go to the next cell down and if there is no text to delete the entire row. I have the bricks I just cant build the house. I need the macro to do this for rows 8 to 500.

View 5 Replies View Related

Delete Rows Which Do Not Contain Certain Text

Feb 21, 2008

I have a spreadsheet which is created 2/3 times daily and I need a macro to do the following;

The columns are A To AJ, I need to delete all columns with the exception of columns M, N, R

Column M contains currency (GBP, Euro)
Column N contains dates
Column R contains various agent log ins, such as LI111222AB, LI222111JM, IFCTest & HOTAYLORL

The row number can vary (Average 1,000), I need to delete all rows that do not contain a log in which begins with LI in column R

Once this is done, subtotal by column R (agent log in), column N, & column M

View 9 Replies View Related

Delete Rows Without Specified Text

Jun 27, 2007

What i'd like is a macro that will delete all cells that don't contain the word pagelibrary somewhere in the cell. As these are weblogs, pagelibrary can be in many different places in the link due to redirects etc, so the macro would have to be flexible with that word. All cells are only one column, in column A. Also if it is possible to delete everything before the word pagelibrary that would be helpful as well as some of these links are very long (and possibly duplicates due to capitalization and redirects).

I've done some searching and this code is the code that closest matches what I want to do. I am by no means a VBA expert; I can decipher a little of what code does, I just can't write it .

Sub DeleteRowEqualTo()
‘Hold Shift Key To delete rows Not equal To

Application. ScreenUpdating = False
Dim currentRow As Long
Dim lastRow As Long
Dim activeColumn As Long

Dim activeValue As String
Dim currentValue As String

lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
activeColumn = ActiveCell.Column

activeValue = ActiveCell.value ................................

View 9 Replies View Related

Delete Rows Containing Text

Aug 9, 2007

I am trying to write a code that would remove rows with any sort of letters in them. I have looked up other ways of deleting rows, but I cannot find the script that would encompass all (alphabetical) letters.

View 9 Replies View Related

Partial Text Lookup In Text String And Return

Mar 15, 2007

Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"

I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.

Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.

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

To Delete All Rows Without Specific Text

May 7, 2013

I need a macro in excel which work like that "To delete all rows without specific text". and following macro for this;

What I need in this is that i have more than one values (highlighted with red in formula) so how i will do in this?

Sub DeleteRows()
Dim lRow As Long
Dim lLastRow As Long

'Change "A" to suit
lLastRow = Cells(Rows.Count, "A").End(xlUp).Row

[Code] .....

View 3 Replies View Related







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