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


ADVERTISEMENT

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

Delete Rows If A Value In Column A Appears Only Once

Apr 7, 2009

I was wondering if it is possible to delete rows if a value appears only 1 time in a column? To clarify since 123 only appears once in column A I want the entire row 4 deleted.

View 2 Replies View Related

Delete Rows So That Student Only Appears Once On Sheet

Feb 13, 2014

I use this code to create sheets for courses but the student appears more than once - see attached Sample (Before sheet).

How can the code be changed so that the student only appears once on the sheet? - see attached Sample (After sheet).

Sample.xlsx‎

View 2 Replies View Related

How To Delete Specific Word From Every Line?

Sep 9, 2013

For example, on cell A1 I have 123456 Total, how do I delete the word "Total"? I have abut 2000 lines. What formula do I use?

View 5 Replies View Related

Delete Specific Word From String

Feb 12, 2009

I want to delete a specific words from string but i have a problem with the code below. For example, i wan to delete the word "Inc" only but the problem with my code is that it is deleting from "Incorporated" too and i want only the code to delete only if it finds the word "Inc" only.

View 11 Replies View Related

Delete Range Names Containing Specific Word

Jun 6, 2008

the active sheet contains a number of defined named ranges. i would like to delete range names beginning with "Sales", yet the number of defined ranges changed from sheet to sheet = for example, Sheet1 may have 3 defined ranges (Sales1, Sales2, Sales 3), Sheet 2 may have 1 (Sales 1). Is it possible to included a wildcard search in the following code?

Sub DeleteRangeNames()
Dim RangeName As Name
For Each RangeName In Names
ActiveSheet.Names(RangeName.Name).Delete
Next RangeName
End Sub

View 3 Replies View Related

Copying Rows That Contain Specific Word

Jan 16, 2007

I am trying to copy rows that contain a specific word from one worksheet to another.

View 9 Replies View Related

Copying Rows That Contain A Specific Word

Jan 16, 2007

I am trying to copy rows that contain a specific word from one worksheet to another.

View 9 Replies View Related

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

Delete Rows With Cells That Contain Some Word?

Mar 4, 2014

How can I delete all rows that have a cell that starts with the letters APP

View 3 Replies View Related

Find A Word And Delete All Rows Above It

Dec 18, 2013

I get a report each week that has a bunch of unwanted header info. It used to be static and the macro just deleted the first 49 rows. I recently found out that the header format changed and it's been deleting crucial data.

I need something that will find the words "Medical Center" and delete all the rows above it, however many that may be...

View 4 Replies View Related

Automatically Delete Rows That Contain A Certain Word

May 15, 2007

I can trying to let the user type a word in to cell A1 and then have Excel remove the rows that contain the word. But VBA below just seem to delete all nonblank rows?

sub delete2()
Range("a2:a200").Select
For Each cell In Selection
If cell.Value = A1 Then
cell.ClearContents
End If
Next cell
Range("a2:a200").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
end sub

how I can update this code?

View 9 Replies View Related

Find Particular Word And Delete Rows Macro

Aug 31, 2012

I am trying to get macro which will find word "Service" in sheet 1 and after that delete that row and below row.

View 1 Replies View Related

Macro To Delete Merged Rows According To Key Word

Oct 5, 2009

I have got an excel sheet with 2 columns A and B.

Column A's rows are merged according to column B. E.g.

Column A:

Row 1-7 (merged) Ron

Column B:

Row 1: New Jersey
Row 2: New Delhi
Row 3: New Jersey
Row 4: New York
Row 5: New York
Row 6: New Jersey
Row 7: New York

My result should be like:

Column A:

Row 1: Ron

Column B:

Row 1: New York

and similarly for other rows also.

PS: The no. of rows merged for column A can be different.

Also the keyword that should be present in Column B should be according to hierarchy, New York, New Delhi and then New Jersey i.e. first the macro should check for New York, if its present (in 1st 7 rows according to example given) then New York should be written in Coulmn B, if New York is not present then it should check for New Delhi and New Delhi should be written in Column B and if New York and New Delhi both are not present then it should check for New Jersey.

There are only 3 keywords which need to be checked i.e. New York, New Delhi and New Jersey.

If these 3 keywords are not present then the macro should not do anything.

View 9 Replies View Related

VBA Deleting Entire Row When Certain Word Appears?

Dec 12, 2012

I currently have the following code that works great and deletes the row from my spreadsheet when the word "vacant" appears in column F.

Sub test()
Dim r As Range
With ActiveSheet[code]....

The problem is, "Vacant" also sometimes appears in column G. So, as this code is now it is finding the word "Vacant" in either column F or column G and then deleting the row.

How can I write the code so that it only deletes the row where "Vacant" appears in column F but ignores it when it is in column G?

View 3 Replies View Related

Excel 2007 :: Counting Number Of Times A Word Appears

Dec 12, 2011

I have a 2007 Excel spreadsheet (saved as .XLS) with worker names in column A and error types in column B. Column B can have multiple entries (which are sometimes duplicative of each other) separated by a hard return.

What I need to do is run tallies to determine the number of errors by type for each person, counting the value every time it appears, even if it is more than once in a particular cell. The ultimate goal is to generate a formula to track the number of occurrences for all error types types for the person in Column A (i.e. one formula each to track ABC's Procedural errors, ABC's Technical errors, ABC's Admin errors, DEF's Procedural, etc) though ideally I just need a formula to calculate any one of those and I can edit it to get the rest. Here's a sample screenshot:

The COUNTIFS formula is where I started but that only seems to count cells with the value as opposed to occurrences of the value. I did find this formula in my searches but it doesn't seem to work:

=SUM(LEN(B1:B100)-LEN(SUBSTITUTE(B1:B100),"Procedural","")))/LEN("Procedural")

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

Excel 2010 :: Find Total Of Times Word Appears Across All Sheets In Column C?

Dec 19, 2013

I have a single work book with 8 sheets (I am using Excel 2010 BTW) and I am trying to find a total of times a word appears across all the sheets in column "C"

I found this formula on another thread. =SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C1000"),"="&H3)) with an example. I made the changes that I needed for my purposes

This worked but only after I renamed the sheets to Sheet1, Sheet2, etc.

Is there a way get the same results from the above formula if all the sheets are named after our reps? Example: sheet1 is named Dan, Sheet2 is Nick, etc?

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







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