I have a ColA that houses Customer Names...
I need to delete any ROW that doesn't have a customer name SOMEWHERE in the ENTIRE Customer Description...
Say this is the COL A, """Campbell Soup/CA (Queensway)"""
if "Campbell" is in row... then DON'T Delete....
i was thinking how the =find() formula gives a number signifing a "find" within a text, but I don't know how to use that in VBA.
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 .....
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 RelatedI 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?
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...
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.................
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.
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
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
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
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 RelatedI 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 RelatedI 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..........
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".
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
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
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
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 RelatedIn 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.
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 RelatedI have a macro that deletes the active row. However if any cell within rows 1-8 are selected then I want the sub to end with an error message i.e. (Cannot delete these rows) The code I have tried is below but doesn't work:
View 2 Replies View RelatedI have an inventory sheet with rows containing a formula. I've placed the same formula in all 65536 rows that Excel 2003 has.
I have a macro to delete unused items. It searches for an item number then deletes that row. (Actually it deletes the item number and a partial row based on an offset, but that was for an earlier version and an entire row would be okay) When a row is deleted, all the other rows move up and Excel creates a new last row (65536) The problem is that this new row has no formulas. While it's probably unlikely that I'll ever blow through 65536 rows, it seems sloppy to leave this unaccounted for.
Is there a way to either add the existing formula(s) to the last row...or insert a new row *somewhere* that is empty except for the formula of the other rows in the sheet? Here's what I have for the existing code.
Code:
'Search for item to delete based on entry to InputBox
Sub Delete_Item()
Dim FindString As String
Dim Rng As Range
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
[code].....
In column A, is a timeline - based on every 15 minutes.
In column H, I use conditional formula to change the cell color of any cell equal to or greater than the value of 00:00:07.
I would like to delete all other rows in column H that is under 7 seconds.
I would like the rest of the worksheet to stay in tact - so I can see the timeline. (or at least column A)
I have tried several macros and vba codes, and it either deletes all the rows or simply does not work with my worksheet.
I am unable to attach a worksheet, but I copy/pasted a simple view. This continues down the page, until 24 hours is complete.
Where 00:00:00 is, I am looking for anything over/equal to 00:00:07 - then delete the rest. The cells are formatted as 'general'.
I am using the formula =IF(ISNUMBER(SEARCH("v",A2)),"OK", "Not OK") to say if cell a2 contains the letter v, then return ok. It would be really helpfull if I could say : if cell a2 contains the letter "v" or the letter "w" or the letter "x", then return ok.
View 2 Replies View RelatedAfter performing several operations and sorting the results I end up with a lot if #VALUE! errors in colum B. I would like to search thru those results and delete any row containg "VALUE! in colum B
View 2 Replies View RelatedI'm trying to find a way to delete blank rows that contains formula. I have two excel sheets.
sheet1 contains information and sheet2 references the values from sheet1. Let's say I put the following values in sheet1:
ColA
AAA
(blank)
(blank)
BBB
(blank)
CCC
After entering above values in sheet1, here's what sheet2 would look like:
ColA
AAA
(blank that holds formula) =IF(ISBLANK(Sheet1!A2)," ",Sheet1!A2)
(blank that holds formula) =IF(ISBLANK(Sheet1!A3)," ",Sheet1!A3)
BBB
(blank that holds formula) =IF(ISBLANK(Sheet1!A5)," ",Sheet1!A5)
CCC
What I really wanted to happen is for macro to eliminate blank rows in sheet2 to look something like:
ColA
AAA
BBB
CCC
Is it something possible?
On my excell I have a lot of data on sheet 2 that i automatically pull form a database.
I have this fomula in a cell on sheet 1.
= SUMPRODUCT(--( 'Sheet2'!P5:P50000=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
The problem is that when the data on sheet 2 gets updated and more lines added, the computer changes my formula for the cell on sheet 1 as well. the new formula will change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50126=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
when there is 126 lines of new data addes in on Sheet2. Id only 50 lines were added in on sheet 2 then the Formula would change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50050=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
I have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:
Example:
Row1: LAST, FIRST, data1, data2, data3, etc...
is there a formula which can "check" the larger sheet for duplicate names (a row with exactly the same FIRST and LAST), and then either:
1) delete these rows from the smaller sheet
2) clear the contents of those rows
3) or at least flag them in some way so I can quickly delete them
it would be quite a task to eyeball and remove these rows one-by-one, so i'm wondering if a formula could somehow do it (I don't really know anything about visual basic)
One other piece of information which might be important:
For these rows containing duplicate first & last names between the 2 sheets, the entire row is not a duplicate entry; only the names will match (columns A & B)... The other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything....
I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
table1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16