Find Text & Delete All Rows Below
May 17, 2008
I have a report that comes in a txt file. After importing into excel i am left with a bunch of garbage that i dont need. This report is anywhere from 5-15 pages depending on how much product was made that shift. I only need the information off of the 1st page. My question is how can i Find the first occurance of specific text (Site) (will be in column A) and have it select that row and all rows below and delete them.
My biggest issue is the first page can be anywhere from 40-60 rows so I need to find the text (Site) and delete everything below it, which could be anywhere from 500-1500 rows.
View 2 Replies
ADVERTISEMENT
Jun 10, 2008
I am trying to create a macro that will delete everything below a certain word.
The code I tried to use is the following. This produced an error:
If Range(Cells(1, 1), Cells(180, 1)). Find(" Interest Expense").Row = 0 Then
iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" NET INCOME").Row + 1
Else
iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" Interest Expense").Row + 1
End If
Rows(iStart & ":" & FinalRow).Delete shift:=xlUp
View 4 Replies
View Related
Mar 15, 2007
Basically i have a column which runs like this:
Company Name:
Name1
Name1
Name1
BLANK
BLANK
BLANK
Name1
BLANK
Name1
Basically, what I am after doing is getting a script to look through that column and delete any rows that do not have "Name1" in Column A.
So far, i've sorted the column so all the blank cells appear at the top, and the "Name1" cells appear at the bottom. Now what I was going to do was use a loop, and I got as far as this before becoming confused .....
View 3 Replies
View Related
Jul 18, 2007
Looking for some VBA code that will look for the first instance of a certain value in Column A, (Begins with 9128),then delete all rows above that row. Then I need to look for a value in Column A that begins with 9129, and delete all rows below that. I've attached a sample workbook to illustrate.
View 3 Replies
View Related
Oct 8, 2006
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
View 3 Replies
View Related
Sep 11, 2013
I need to loop through data when it finds a blank it need to delete that row and 15 rows down and loop through whole data set find blank row and delete 15 rows down.
It's been years since I did any VBA, and I forgot. Do I need to use offset to acheive this task?
View 6 Replies
View Related
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
Feb 23, 2009
I have searched high and low for a more efficient macro than the current loop i have. I have a range that varies in size but always follows the same format. After i reach a certain value output from a formula, "4", I want to delete all lines below that line. So I want to find value "4" then move one row down and delete all lines to line 2004. I have attached the macro that i compiled but it takes a couple of minutes to run.
Sub manual()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView.................
View 2 Replies
View Related
Aug 6, 2008
I am trying to select all the data in every row below where the first "flat" entry in the worksheet is but it will only go to the bottom of the data in the column where it finds flat. Some data in other columns extends beyond this so it is not working for me. It would work for me to select everything from where it finds flat to the very last row 65536 but I can't figure out how to do that with what I have. I am then deleting all this data.
Cells.Find(What:="flat", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlDown)).Select
View 3 Replies
View Related
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
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
Mar 14, 2008
I'm trying to come up with a formula to find the last row without any data in columns E and F and delete the 10 rows below them. Is there a way to do this?
View 3 Replies
View Related
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
Jun 4, 2007
I have a worksheet that is several thousand rows long. It is sectioned into various "topics" (e.g. Revenue, Profit); each topic (there are about 100) has 10 companies. The 10 companies are the same for each topic. For instance, if 2 of the companies are Microsoft and Intel, then there will be a Microsoft row and an Intel row within each topic.
I am trying to write some code to delete every instance of a company (the user indicates the company in a form). So, for instance, if I want to delete Microsoft, the user would enter the stock ticker, MSFT, into the form and press the appropriate command button. When the command button is pressed, the code should find each row that contains the ticker MSFT (the tickers happen to be in column B) and delete it.
I have written the following unsuccessful
Private Sub CommandButton1_Click()
Dim Ticker As String
Ticker = TextBox1.Value
'If IsText(Ticker) Then
Range("$A$5").Select
Cells.Find(What:=Ticker, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate..........
View 9 Replies
View Related
Sep 12, 2008
1) Highlight column A, and do a search on the word "Item"
2) Once it finds the word item, move 2 rows above it, and delete all rows above the word "Item"
The word Item at times can be on any row, and its very random, but its always in column, A. I tried doing it with a regular macro, but it seems to remember the row that I used, rather than two rows above the word "Item".
View 9 Replies
View Related
Jul 31, 2008
I am trying to write a macro to clean up a CSV file which automatically hard codes titles throughout the spread sheet. I am getting an error on the "FindNext" method below. I am getting "Unable to get FindNext propety of the Range Class" error. Also, the code for the For loop for multiple lines does not appear to be working.
Sub cleancsv()
lastRow = Range("A1").End(xlDown).Row
pmpt = InputBox(Prompt:="What text are you looking for?", _
Title:="Text", Default:="i.e.: Finished Goods Inventory")
numrows = InputBox(Prompt:="How many rows to delete (counting original):", _
Title:="Number of Rows", Default:="i.e.: 1")
Set cell = Range("A1:A" & lastRow). Find(pmpt)
If Not cell Is Nothing Then
For l = cell.Row To cell.Row + numrows
Range("A" & l).EntireRow.delete
Next l
End If
Do
View 8 Replies
View Related
Dec 2, 2009
I need to change the value of cells if column I contains the word error ....
View 14 Replies
View Related
Sep 3, 2009
VBA for finding text through an Excel SS and deleting the entire row if the text is found.
For example if the string "STAD LLL" is present in any cell in any string position the entire row is then deleted.
View 9 Replies
View Related
Feb 24, 2009
I have over 2000 rows with 20 columns and i need to find and remove identical rows. I can do a match with MATCH() by concatenating the columns and comparing them as a whole but i need to apply this to the whole table and i need it to remove all the duplicate rows it finds.
View 5 Replies
View Related
Apr 24, 2009
In the code below I find rows containing "$$ #" at the far left and process it.
For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.
View 2 Replies
View Related
Dec 28, 2013
I'm looking for code to find a copy of the text in cell AH3 in a range AH7:AH100 then delete all copies of the text in that range AH7:AH100 leaving other cells in that range which contain different text intact.
View 3 Replies
View Related
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
Sep 26, 2007
I am trying to move through the worksheets and delete all columns with "Accession" in contained in them. I have to do this with an external macro as the spreadsheet with the data is created from another program. I tried this and get an 'Object or With block variable not set' error and the debug highlights the Cells. Find line of the code.
Also, the number of columns could be variable within the spreadsheet as it is compiled by the other program.
Sub DelAccessionNum()
Dim Wrkst As Worksheet
For Each Wrkst In ActiveWorkbook.Worksheets
Cells.Find(What:="Accession", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
On Error Goto Completed:
Selection.EntireColumn.Delete Shift:=xlToLeft
Completed:
Next
End Sub
View 9 Replies
View Related
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
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
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
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
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
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
Aug 1, 2013
Each day I recieve a spreadsheet with thousands of row. In col A there is data that has "-NCA1" or "NCA2" as the suffix. I have to "delete" these rows before proceding.
I'd like to know if there is a way to do this with a macro and ideally delete a wildcard suffix with "NCA*" in case any 3...4..5 etc are added.
View 4 Replies
View Related
