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


ADVERTISEMENT

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

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

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

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

A Macro To Delete A Row That Does Not Contain A Given Word

Jun 9, 2009

I am trying to find a macro that deletes a row that does not contain a given word or string.

For example, if a row does not contain the string -> then delete it or even better select and move all such rows to another worksheet so that the main worksheet is left only with rows containing the string.

View 7 Replies View Related

Macro For Finding Specific Word And Deleting It

Apr 2, 2014

I have a excel file with more then 10 sheets..Some of the sheets contains this word in some random cells" #DIV/0! " I want a macro which can find it in every sheet except parent sheet and can remove it.

View 5 Replies View Related

Macro To Find A Specific Word And Insert Cell

Jan 12, 2010

I have a large spreadsheet with about 18000 rows or data and about 60 columns. I need to a macro to find a specific word in a column like "charge" and anytime that word is found in the column to insert a blank cell in front of it.

Currently the sheet looks like this:Fee AChargeFeeChargeFee AChargeFeeChargeFee AChargeFee AChargeNeed to get it to look like this: (so I can sort and subtotal)Fee AChargeFee AChargeFee AChargeFee ACharge

a macro to the for whichever column I choose.

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

Macro To Find Word And Delete Group Of Cells?

Aug 1, 2014

I have an excel file, where there is a section "Additional Requirements". This section is repeated like 20-25 times in the sheet and has a group of merged cells below it to enter data as shown in the picture. Issue is that majority of these "Additional Requirements" are not filled by the user. Hence maybe only 3 or 4 are filled and I have to manually delete the rest. I tried the code below, but it doesn't do anything.Running the macro just jumps the cells slowly downwards the sheet. Maybe if the macro runs 10-15 times it reads the whole code and then moves on to the next row.8-1-2014 12-43-27 PM.png What I'm trying to achieve is:

1. for the macro to find "Additional Requirements"

2. Then check the Merged cell below it (thus the offset)

3. If this cell is blank, select this cell and the cell containing "Additional Requirements"

4. Delete entire rows of these cells/delete the selection (entirerow.delete). (I read somewhere to set a variable as selection and clear it, hence ive included in the unreadable code. This wasn't working either)

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







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