Delete Rows With Specific Characters In A Specific Column

Dec 10, 2007

Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):

Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete

although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:

Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next

but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...

View 5 Replies


ADVERTISEMENT

Delete Rows Where Columns Have Specific Lower/Upper Case Characters

May 20, 2008

I am trying to detete rows that have several specific values, upper and lower case, (A-H) in any of several columns (B through H). I can delete upper and lower case "D"s in column B, but I'm having difficulty stringing together several variations and getting at the "D"s that are midstream (such as: ADeC), I tried ("*D*") to no avail.

With . Cells(Lrow, "B")
If Not IsError(.Value) Then
'****
If LCase(.Value) = LCase("D") Then .EntireRow.Delete

View 5 Replies View Related

2003 Delete Rows With Specific Value In Column C

Apr 13, 2009

I need a code that will go thru column C and delete the whole row if cell value is 78315, Also it needs to shift the the rows up so there is no empty rows wheneverything is done done. I used some codes but they take forever to go thru whole sheet.

View 4 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 Specific Text In A Column

Apr 16, 2008

I want to delete any/all rows from a worksheet (named UK) which have the word "VAN" in column P. I have tried using a 'For Each... Next statement' in a macro, but always get a debug box "Run Time Error '91' Object variable or With block variable not set". What am I doing wrong? my code follows

Sheets("UK").Select
Dim cell As Range
For Each cell In Worksheets("UK").Range("P10:P200").Cells
Cells. Find(What:="VAN", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
Selection.EntireRow.Delete
Next

View 3 Replies View Related

Delete A Row If It Has A Specific Text In A Specific Column

Jun 21, 2006

I need a code that will delete a row if it has a specific text in a specific column.

In this case in column W if there is anything that starts with '>TC' I need that entire row deleted that its in.

View 7 Replies View Related

Macro - To Delete Whole Rows After We 'control' Find Something From A Specific Column

Aug 26, 2008

I am not the best at this, but we recorded a macro and we want to delete whole rows after we 'control' find something from a specific column. WHen we recorded our macro, it finds the first instance and we hightlight the whole row and 'control' 'shift' 'end' and delete all of the rows. We did this because we sorted and made sure the information we wanted to delete was at the bottom of the worksheet. we realized that each day the spreadsheet we pull from an ip address gets bigger and the row that we started from to delete starts on a different row each day. How do we get all the information to delete everyday, even when the row that Owned starts on changes? ...

View 9 Replies View Related

How To Delete All Words Containing Specific Characters

May 14, 2014

I need a formula which can clean up a huge data set. Essentially i need to delete the entire word which contains the characters "aceae". note that "aceae" is a suffix, but i need to delete the entire word not just the suffix, plus keep the rest of the string. i have tried the "find and replace" function of excel with wildcard, but that deletes everything before/after without deleting the entire word. i have tried a combination of formulas to isolate the unwanted words, but that method is inefficient and inaccurate. below is a schematic of what im looking to do:

Column A ------------------------------ Column B
l. planeri asteraceae africa laselva-----> l. planeri africa laselva
l. planeri moraceae europe singer------> l. planeri europe singer
origin l. fluviatilis bignoniaceae asia----> origin l. fluviatilis asia
alternate l. fluviatilis piperaceae asia---> alternate l. fluviatilis asia

View 6 Replies View Related

Delete Characters After Last Specific Character Occurence

Jun 11, 2008

I have one column with many numbers. Some have one dash and some have two.

Example:
123-123456-65
012-789546-1
98B12354-889

Is there a way that I can remove all characters after the last (dash) in the number?
Example:

If number is 123-123456-65
Then 123-123456

If number is 98B12354-889
Then 98B12354

If someone could just lead me in a direction, I might be able to figure it out. However, my code is elementary and most of the time, I record macros and the play with the code until it does what I want.

View 3 Replies View Related

Finding Rows With Specific Characters - Copy Only Those Rows

Apr 7, 2012

I'd like to be able to search a spreadsheet for a specific word or series of characters. Once all of the ROWS that contain the characters are found, I'd like to be able to have another spreadsheet with ONLY those rows (all columns).

View 8 Replies View Related

Delete Rows With Specific Value

Jul 27, 2012

I created the following macro to remove all rows that contain the value 'Shutdown" in column F

Code:

Sub DeleteUniqueValues()
Dim LR As Long, i As Long
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
With Range("F" & i)
If WorksheetFunction.CountIf(Columns("F"), .Value) = Shutdown Then .EntireRow.Delete
End With
Next i
End Sub

The macro runs without error, but when I checked the spreadsheet, rows matching this criteria were not deleted.

View 4 Replies View Related

Delete Specific Rows

Sep 13, 2007

is there a code to chose certain rows using there numbers (Row number 3 to row number 9) to be deleted? yes, there is a code to delete the selected rows, but what i am after is a code to chose rows by thier numbers like delete from row3 to row9?

View 7 Replies View Related

Delete Rows Which Contain Any Specific Identifier?

Jul 29, 2012

I'm looking for a code where all those rows which contain some identifier be deleted.

Eg, If any row contains identifiers like "----" , "PROG:" , etc shall be deleted.

NOTE: The identifiers are found on the A Column of any row.

All rows which do not contain the above identifiers shall remain untouched.

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

Delete Rows Without A Specific Value In A Cell

Jun 2, 2008

I have 2 spreadsheets of delivery information. Sheet 1 contains data of all stores in the UK with addresses. Sheet 2 containts contacts of all stores that promotional matrial needs to be sent to.

The only common cell in a column of 4 digit store numbers.

I need to delete all rows from sheet 1 that do not contain a value in the store number column that is displayed on sheet 2.

View 11 Replies View Related

Delete Rows Which Contain Specific Data?

Nov 1, 2012

Code in VBA which I can auto delete many specific rows.

example: in column B got the word "Tester, Dummy, Display and Mini.."

I would like a code which automatically delete the rows which contain the text as above.

View 6 Replies View Related

Delete Rows Where Cells Don't Contain Specific Value

Feb 27, 2013

If I have a column of data called "ColStream" and want to delete all rows that don't contain the string "Production", whats the best way to achieve it?

I have tried a for each loop, with INSTR but as this moves down a cell from the cell whose row has just been deleted, skipping the "NEXT" row in the range, it doesn't give the correct results.

Incidentally, the end game is to copy all the remaining cells to the cell immediately below a cell called "StreamsDatabaseStart" in a worksheet called "ProdStreams" should anyone out there be feeling particularly charitable!

View 4 Replies View Related

Delete Rows If Specific Value Is Returned

Feb 23, 2004

I need a formula that finds a value or range of values in a list and if the value meets the criteria automaticaly delete that row. For example I want to search B3:B7 for a 0 value if I find a cell in that range with a 0 value i want the formula to delete the entire row.

View 4 Replies View Related

Delete Tab(s) If Specific Rows Are Blank

Jan 31, 2008

A macro that will delete a tab or tabs in a file if and only if rows 11, 13, 23 & 25 are completely blank within that tab?
So basically ALL rows would need to be blank, if there is any data within any of those rows, then tab should NOT be deleted.

View 9 Replies View Related

Delete Rows That Contain Specific Words

Nov 10, 2006

how to create one macro: I have a spreadsheet with 8 columns with various text headings. Each column has different data (some show dates, some show only figures, some show text). Specifically, column E (entitled "Sub Item Reference") has various text entries (e.g. "Newsletter" or "Booklet"). I am hoping to find a simple macro that will search column E and delete any rows where that column contains the text "Cover" or "Label". I'm sure it's the simplest thing going but I can't seem to work it out.

View 7 Replies View Related

Delete Associated Rows Not Containing Specific Names

Jun 27, 2008

I have a code that goes through and deletes everything that doesn't contain a certain word, but I have several words ( Names actually) that I need to keep in my sheet, but all others can be deleted, along with their rows...the names are on in Column B...there are about 11 names currently and probably be adding a few more next month...This is a macro that is set for work to go through like I said a lot of Rows and delete all them but those that have the names of the workers I need to keep in the sheet...the names are scattered throughout, which is the reason for a need of a macro to do it for me cause it takes me forever to manually do it each day...The code for just one person I can't get to do multiple names so this is what I came up with based off what I know plus reading hundreds of pages on the net regarding similar style needs, but none exactly the same....

This is the Code that I'm trying to use but unfortunately I get Type Mismtch Error 13 ( I think it's error 13)...what am I doing wrong? (I only am working with one sheet that contains all the data...

Sub SortOutMyTeam()
Dim wst As Worksheet, wstNew As Worksheet
Dim rData As Range, r As Range, rTotal As Range
Dim avNames() As Variant
avNames = Array("Name1", "Name2", "Name3", "Name4", "Name5")
Set wst = ActiveSheet
Set rData = Range("B1:BL" & Cells(Rows.Count, 1).End(xlUp).Row)
Application. ScreenUpdating = False
wst.AutoFilterMode = False
With rData
For i = 0 To UBound(avCrit) Step 2
. AutoFilter field:=3, Criteria1:=avNames(i), Operator:=xlOr, Criteria2:=avNames(i + 1)
On Error Resume Next
Set r = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells

View 5 Replies View Related

Find Rows Containing Specific Data And Delete

Jan 5, 2010

I have a large report that I pull each month listing which products we sold last month. There are certain products I don't want included in the report. Until I can have the report re-written, I'm looking for a macro or a function that will find all the rows that contain these product numbers in column M and delete the entire row. The product numbers I need to delete are always the same, but some months one or more of them might not be in the report if they weren't sold.

In Summary:

Find in column M the product numbers:
c1000
316140a
316140
316295a
316295
316311a
316311
316451a
316451
316450a
316450
316452a
316452

Delete those rows containing the product numbers above

View 6 Replies View Related

VBA Code To Delete Rows If Specific Value Is Not Present?

Aug 6, 2014

I'm trying to write a macro to update a report including deleting all rows that do not contain a specific value of TP in column K. Below is one of the examples.

[Code] .....

View 3 Replies View Related

Find Rows Containing Specific Data And Delete?

Feb 28, 2014

create a VBA to delete rows if the cell doesn't have "Other".

The data is in Column C which consist of the below data so I like VBA to delete the rows when the cell doesn't have "Other"..

-Primary
-Business
-Home
-(blank)
-Other

View 5 Replies View Related

Macro To Look For A Specific Word And Delete Rows

Jan 27, 2009

I need to build a macro which will look for a specific word say :"ABC" in a particular column say "B" and delete 5 rows including the row containing the word "ABC"

View 6 Replies View Related

Command Button - How To Delete Specific Rows

Mar 15, 2012

I have a command button that upon its click I would like to delete a specific row. I am using a vlookup which is giving me the row of the number I would like to delete. How do i go about deleting out this row?

this is also a radiobutton within the row that I would like to delete out as well. It is not named but it linked to column B within the row that I will be deleting.

View 3 Replies View Related

Macro - How To Delete Rows With Specific Criteria

May 21, 2012

I was searching for a macro to delete rows that obey a specific criteria. I downloaded [URL]..... and copied the Loop Example to my WorkBook.

When using Mr. de Bruin example, it worked perfectly, it did really delete the rows that obeyed my ">2" criteria. Although, when using on my workbook, the macro deleted the values, not the entire rows. Here's what's happening:

I would like to delete rows, not only values.

Code:

'The code below will delete every row in the usedrange with "ron" in the A column.
'If .Value = "ron" Then .EntireRow.Delete
'
'I use the A column in my example, change the A to your column in this code line.
'With .Cells(Lrow, "A")
'
'Change ron and the A to your search word and column before you try the macro.
'See the examples on the worksheet if you want to do something different.
Sub Loop_Example()
Dim Firstrow As Long

[Code]....

View 2 Replies View Related

Macro To Delete Specific Range Of Rows

Jul 12, 2014

I currently had to start from scratch as my formula kept raising the debug screen. I know the answer is simple but I cant figure out what it is. I have column A, rows 9 through 1000 with data. I want to delete the entire row of any "x" value present in column A rows 9 through 1000. I am new to VBA. So simple is good.

View 2 Replies View Related

Delete Rows :: Where Specific Word Appears

Jul 22, 2008

I have a spreadsheet that I need VBA Code to do the following

1) Delete all rows where the word totals appears in Column B for eg "Totals for Vehicles Stocked in 0706" "Totals for Vehicles Stocked in 0707" etc

2) Delete all rows where the value in col m is less than 45

I have written the code, but cannot get it to work-see my code below

Sub Del_TOTALS_Underaged()
FinalRow = Cells(65536, 2).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 2).Value >= "Totals" Then
Cells(i, 1).EntireRow.Delete
If Cells(i, 13).Value < "45" Then
Cells(i, 1).EntireRow.Delete
End If
End If
Next i

End Sub

View 9 Replies View Related

Delete Rows With Specific Text (multiple Possibilities)

Nov 13, 2013

I need a VBA macro that deletes whole rows if the cell in one of the columns does not match one of three texts. For example, in column B, if the text in there isn't "Mary", "Joseph", or " " (empty space), the row must be deleted.

View 4 Replies View Related







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