Delete Row If Cell Text Matches Known String

May 21, 2008

I have an excel file, and on some rows, the "B" column contains the text " Total:" (it does have a leading space). I would like to loop through the first 200 rows (maximum length of the file) and delete all the rows that contain " Total:" in column "B". I have tried:

For i = 1 To 200
range("B" & i).activate
If range("B" & i).value = " Total:" Then
rows(activecell.row).select
selection.delete shift:=xlUp
End If
Next i

When I step through that code, it just skips over the IF function as if B1 does not equal " Witness:"

View 3 Replies


ADVERTISEMENT

Delete Rows If Column Has Fewer Than X Matches To Part String Match

May 5, 2008

I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.

Data set example:

In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.

Dim c As Long
c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
With Range("A3:A" & c)
If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then
. AutoFilter field:=1, Criteria1:="1/*"
.Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete
.AutoFilter

I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.

View 6 Replies View Related

Delete Row If Cell Contains Certain Text As Part Of Text String?

May 15, 2014

I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.

View 5 Replies View Related

Delete A Cell If It Contains Specific Text String?

Oct 19, 2012

I need A VBA that will delete a cell if it contains a specific text string

As long as the cell content is deleted, I can do the rest that needs to be done.

View 4 Replies View Related

Finding Text That Matches Format Criteria In String?

Oct 3, 2011

I'm trying to restructure a list of files at work into a format that makes some sort of sense and can be filtered into useful information.

Part of what I need to do, is match up all the drawing numbers that a certain project references. The numbers are formatted like "####A##" ex. 1234A01,1234A02,5678A01, etc. The cells that I need to extract drawing numbers from contain some description or other text (not just the drawing number) so I need to extract the drawing number from that cells value. (Ex. I need to get the drawing number 1234A01 from a cell whose value is "blah blah 1234A01 blah blah blah")

I was thinking I could just search the string for "####A##" but I'm not really sure how....I tried instr, but I think its searching for the literal value of "####A##" rather than treating the # characters as wildcards...

I tried:

Code:
if rngNames.Cells(intx, 2).Value Like ("*####A##*") then
set intStart = instr(1,rngnames.Cells(intx,2).value,"####A##")
'....
end if
but intStart remains 0, so this method is not working...

View 3 Replies View Related

Ignore Character String That Matches Another Cell

Mar 2, 2012

I have a cell that contains a string of characters that were combined from two different cells. What would the formula be for excel to ignore the characters that are from one of the cells? In other words, if cell A1 contains ABCD123 and cell A2 contains XYZ (the length of the string in A2 varies in length), the combined string is ABCD123XYZ. In cell B1 I would like excel to ignore the contents of cell A2. This seems simple but I'm not coming up with the right syntax.

View 6 Replies View Related

If Text Of Cell In Range Matches Text Of Cell In Column - Match Formatting

Jul 5, 2012

Trying to create a button that, once clicked performs the following task:

Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.

If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.

Only 1 cell in the range will match 1 cell in the column

For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background

During click:
matches these cells
changes X4 so that it also has a yellow background

After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background

View 3 Replies View Related

Delete Row If Specific Cell Value Matches Value Found In Another Worksheet

Mar 22, 2011

I need a formula or VBA macro to do the following:

I have 2 worksheets, namely sheet 1 and sheet 2.
Sheet 1 contains data in columns A to H.
Sheet 2 contains data only in column A.

I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.

View 4 Replies View Related

Delete Rows If Opposite Column Cell Matches

Sep 6, 2006

I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!

Sub DeleteRepeatedRows()
' goes through coord. sheet and identifies duplicated lines, copies the date from the old row
' copies it into the new line, then deletes the old line
Dim rCell As Range
For Each rCell In Selection
If rCell.Value = rCell.Offset(1, 0).Value Then
rCell.EntireRow.Delete
End If
If rCell.Value = rCell.Offset(2, 0).Value Then
rCell.EntireRow.Delete
End If.........................................

View 7 Replies View Related

VBA To Delete Part Of Text String

Aug 15, 2014

I am trying to delete part of a text string for a range of cells. The code I have so far deletes the first set of letters but is looking in every cell in the range.

Ideally I would like it to only look in the cells of col B where there is data then only delete the first 10 letters if the cell begins with "Service ID"

Sub DeleteServiceID()
Dim c As Range
For Each c In Range("B1:B1000")
c.Value = Right(c.Value, 10)
Next c
End Sub

View 4 Replies View Related

Delete Middle Part Of Text String

Mar 30, 2009

I am looking for a formula to delete information from the middle of a text string. I have ~ 1,000 lines of data. Each text string is composed of the same way. The data I am looking for is two parts of the string (1) all the text up to and including the first “%” character (2) the last six characters.

Raw Data
CARDINAL HEALTH 5.5%13CARDINAL 5.513 CARDINAL HLTH 5.5 061513
UST BILL 0%09US TREAS BILL 0%09 UST BILL 0.0 092409
WORLD SAVINGS 4.125%09WORLD 4.125%09 WORLD SVGS 4.125 121509
JP MORGAN 5.375%12JP 5.375%12 JPMORGAN CHS5.375 100112

Desired Result
CARDINAL HEALTH 5.5% 061513
UST BILL 0% 092409
WORLD SAVINGS 4.125% 121509
JP MORGAN 5.375% 100112

View 2 Replies View Related

Cell Formatting - Red Text If Cell Matches Certain Condition Based On Another Cell

Sep 24, 2013

I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)

When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.

I need the text in column AQ (the monetary amount) to become red when the the opposite is true.

i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.

View 2 Replies View Related

Find Specific Text In Cell That Matches Range Then Display Specific Text?

Aug 5, 2013

I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.

View 2 Replies View Related

Macro To Jump To Cell That Matches Text Selected From A Dropdown List?

Mar 24, 2014

I'm looking for a macro to jump to and select a cell if it matches the text in another cell based on a drop down list. So I have a drop down list in cell c57 and a form control search button right next to it. I want the user to be able to select from the drop down, click search and then jump to the cell that matches what they selected. I would like the macro to search from B:60 - B629.

View 4 Replies View Related

Conditional Or VLookup (Gives True Or False If Text In A Cell Matches Any Cells In A Range)?

Jan 14, 2009

I need to create a formula that will give me True or False if text in a cell matches any cells in a range.

For example:

Column A and B have text:

Account
Date
Name
Species

Column C has other and sometimes matching text:
Name
Deal

What formula would I use to find out if all cells in Columns A and B are represented in Column C?

View 3 Replies View Related

Delete Row If Cell Does Not Contain Certain String

Oct 23, 2013

I want a macro to look at column G and if a cell in column G does NOT contain "Test Case" anywhere in the cell then delete the row. So for example G5 might be "Test Case/Others" - I would not want this row deleted.

View 3 Replies View Related

Find Sub String Matches Between Two Columns?

May 30, 2014

I am trying to match words in two separate columns that are not exact matches. any formulas or codes I can write to do this.Below is an example.

Column A
3m Corporation
Apple Inc
Allstate Corp
State Farm

View 2 Replies View Related

Formula To Count Matches In A String

Dec 19, 2008

I need a formula that will count the number of pairs of digits in Col H that matches the pairs in Col's E:G....

View 9 Replies View Related

VBA To Replace String That Matches Pattern

Nov 22, 2009

I have Userform1, Textbox1, Textbox2 and CommandButton1. Textbox1 is being manually inputted by users.

I would like that when I click Commandbutton1, that Textbox2 will be populated by whatever value was entered in Textbox1, except that the any string that matches any of the listed pattern below:

009???
017???
088???
078???
003???
663???

will be excluded. "?" here represent any 1 character.

So for example, if the user inputted "AA-90-088100ABC" in Textbox1, when CommandButton1 is clicked, Textbox2 should have the value:

"AA-90-ABC"

View 9 Replies View Related

Search A String For A List Of Matches

Nov 30, 2006

I am looking for a way to check for the presence of a member of a list in a field. Not for an exact match, but for the text to be somewhere present. I know I can use the "search" function to find text within text, but I don't see how to find the presence of any member of a range of values (a list) within the text.

View 9 Replies View Related

Delete String After Last Space In A Cell

May 4, 2007

I am interested in some sort of macro or formula that will strip all of the characters in a cell that come after that last space. My difficulty lies in that the string isn't a constant character count nor is the string always the same.

American Legion Post 8754 Hartford
YWCA Canton

I would like for it to say:
American Legion Post 8754
YWCA

View 10 Replies View Related

Delete Entire Row If Cell Contains String

Jan 30, 2008

I need to write a macro that will search column A and find the word hospital or HOSPITAL and if found delete the entire row. The code I have below will do this ONLY if hospital or HOSPITAL is the only word in the cell. However in my column A there are numerous words in each cell i.e Saint Mary's Hospital.

Sub KillRows()
Dim rngNew As Range
Dim rngDelete As Range
Dim aCell As Range
Dim lastrow As Long
Set rngDelete = Nothing
Set rngNew = Worksheets("Sheet1").Range("A1", Range("A65536").End(xlUp))
rngNew.Select
For Each aCell In Selection
Select Case aCell.Value...........

View 5 Replies View Related

Compare Text Cell With Numeric Cell And Output Text String

Mar 25, 2014

As per title, I am trying to compare a column of text cells which contain "Yes" or are empty and a columns of numbers. If they are "Yes" and "1" on the same row, I want to output an "OK" message. Excel seems happy with the following code but it does not work and returns an empty cell if the two conditions are true.

[Code] .....

View 6 Replies View Related

Copy Any Bold Text Within Cell Text String To Adjacent Cell?

Jul 31, 2014

Here's an interesting one:

A1 contains a text string which is both bold and unbold (
B1 is blank

I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.

Example:

A1
The sky is blue

Macro is run

B1
sky blue

View 11 Replies View Related

Summarize Data Based On Partial String Matches

Jun 30, 2006

I have two questions:

a. Check the code below:

Dim news1 As String, news2 As String

news1 = "new"
news2 = "polygon"
Dim countie As Integer
For buddie = 2 To b1.UsedRange.Rows.Count
'If b1. Cells(buddie, 1).Value Like "new*polygon" Then
If b1.Cells(buddie, 1).Value Like news1*news2 Then
countie = countie + 1
n1.Cells(buddie, 10).Value = "test"
End If
Next buddie

I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.

How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.

b. If i have a value in the cell:
/new/blahblah/anycra/polygon
how do i split it so as to assign two variables to have 'new' and 'polygon'.

View 9 Replies View Related

VBA To Delete Matches

Oct 8, 2008

I have an issue where I want to delete a row on Sheet1 if the cell in column a of that row is a match in a column A of Sheet2. If there is no Match, I want it to move to the next row until it has checked all rows with information. The size of the data varies daily. I do not want to check Row 1 either. Below is an example of what would happen.

BEFORE MACRO:
Sheet 1 Column A
ID#
A
B
C
D

Sheet 2 Column A
ID#
A
C
D

AFTER MACRO:
Sheet 1 Column A
ID#
B

Sheet 2 Column A
ID#
A
C
D

View 9 Replies View Related

Search Cell For Text String And Paste Text In Based On Finding?

Jun 29, 2014

I need to create a macro to do the following:

Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.

For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.

View 7 Replies View Related

Check If Text String In Cell With Other Text Is In List

Mar 27, 2008

I have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?

There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.

View 3 Replies View Related

VLookup For Text And Add Relevant Values If Text Matches In Range

Jun 19, 2014

Check the attachment, i could not make out this using vlookup, how to overcome this problem.

test.xlsx‎

View 2 Replies View Related

Cross Check Data In Two Sheets And Delete If It Matches

Mar 29, 2014

creating a macro I have two sheets named customer list (I have only put in 30 rows as an example but some sheets have 400 rows)

in the sheet named list column "F" are the names it should be cross checked with column "F" in the sheet named customer if it is there entire row to be deleted

View 3 Replies View Related







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