I have a file with 1000+ rows. The values in column A have text that I want to keep and then extraneous text tacked on. The good thing is the extraneous text ALWAYS start with the same characters. How can I systematically delete the extraneous text values at the end?
View 7 RepliesI'm using excel to parse txt a file with account numbers in it. If it finds a dodgy number I want it to delete the entire line in the text file. I have manage to write the code that opens the file and locates the dodgy numbers, however I'm not sure whether it is posible to select the line and delete it.
View 6 Replies View RelatedI have an array where every cell's text starts with "Sum of" then the rest on the information. Each cell's text is different except for the "Sum of". There are thousands of cells. Is there a way to select them all and edit out the "Sum of" part only and leave what is left?
View 3 Replies View RelatedI have a sheet where individual actions are listed and then subtotaled.
ElementError Code
Action NAC-01
Action NAC-02
Action NAC-07
Action NAC-08
Action NAC-14
[Code] .......
I need to remove all the subtotal rows.
I have a spreadsheet nearing 10000 various products with columns A-M.
I need to do the following:
Delete the entire row and shift up
If column H contains "this text" or "File Server Manager" or "ABT". Column H being the product description.
Basically, I have customers that are authorized only to sell certain products. So instead of filtering threw and saying, ok he cant sell "File Server Manager" delete that entire row. OR he cant sell these 10 products cause it says oracle in the product description then delete that row.
I was researching the fourms and saw something on AutoFilter. Will that work?
It was something along the lines of:
.AutoFilter field:=1, Criteria1:="ENTER TEXT HERE"
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
I appreciate all your help!
Sincerely,
James
I have downloaded my pay pal transactions and need to go through and delete rows of debits that are not debits for my business, but do not know how to do this automatically. I know very little about excel so I hope this can be fairly easy.
View 9 Replies View RelatedI have the following numbers in excel, with letters next to it, for example:
8.1b
56.8ab
5.7ac
I want to remove the alphabets, and be left with just the numbers, example:
8.1
56.8
5.7
A2 contains the word Yes. If A1 is blank, I need to clear the text from A2. Can this be done with conditional formatting? I know I could just change the text to white, but I actually need the text cleared from A2 if A1 is empty. My boss doesn't want me to use VBA so I can't code this.
View 8 Replies View RelatedI'm developing a fixture list (involving 26,000 individual fixtures) I'm trying to come up with a Macro which will enable me to delete part of each cell containing the two team reference numbers and the match number, and paste it in another column. I'd like to do this for all 26,000 fixtures.
In case you didn't understand that too well. For example, with the cell value of '1v2-54', I'd like to get rid of the '-54' and put it in another cell.
But the key thing is, using this principle, I'd like all 26,000 fixtures to be done this way. Obviously I'm not going to do it by hand.
As you can see from the screenshot, the pattern of how the column goes:
Id like to thank ByTheCringe2 for getting me to this point. Now I need just a little bit more help before I can take over and work on it on my own. I have attached a sample sheet of the current problem im having. I messed up in the previous post by not giving an example of text underneath invoice.
This is what I need.
1) I looked in to this and I think the best way to go about this would be to say that if a cell in Column H does not have a numeric value, then to erase the entire row. The reason for this is that Column H is the only column that remains constant in that it is the total balance of the invoice therefore if it doesn't have a balance or there are letters in the cell, it is not a valid cell and not a valid row. I highlighted the example of my problem. This is the formula im using...
=IF(ISERROR(MATCH($E20,'Imported Data'!$E$6:$E$30,0)),"",IF(INDEX('Imported Data'!K$6:K$30,MATCH($E20,'Imported Data'!$E$6:$E$30,0))=0,"",INDEX('Imported Data'!K$6:K$30,MATCH($E20,'Imported Data'!$E$6:$E$30,0))))
A cell contains the name, tel number and address of a user. Those 3 fields are separated by a carriage return (alt+enter) in the cell. I need a macro to keep only the name, which is on the first line of the cell. The macro should be generic and should work for any particular name.
John Smith
96783456
Hollywood Street
In a rage of rows I have some rows that are blank - without any text or data. Is there a macro I might enter that will look at the range of rows, determine which are blank and then delete the blank rows?
I want to create a macro that will find a text string called "Season Average" in the entire workbook and delete a range of cells above it 4 cells longs starting right above the text and then going left.
Example is if "Season Average" is in cell D11, than I want the cells D10, C10, B10 & A10 Deleted and the cells to shift up
This happens throughout the entire workbook and multiple times on each work sheet in different spots not consistently same cells.
I am trying to figure out how to create a Macro code in Excel that will delete text file page breaks. Each page break starts with a square in column A and ends with the word continued in column D. I've tried several times, but when I test the code and I highlight the section to delete, it only deletes those specific rows next time. How can I get it to delete the rows from the square to "Continued" throughout the document without making reference to specific cell numbers?
View 3 Replies View RelatedI have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.
The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"
I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?
View 14 Replies View RelatedIn Excel 2007, when I delete a row/cell, text is associated with deleting row/cell but some lines, polygon ... can not associate with deleting row/cell . So the lines, polygon ... in mistake location.
View 12 Replies View RelatedI am using this code to populate column J with a date when yes is selected in colum I.
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = Columns("I").Column Then
If .Value = "Yes" And IsEmpty(.Offset(, 1)) Then .Offset(, 1) = Date
End If
End With
End Sub
However i also want any value entered in J to be deleted when No is selected.
My original datasheet had two separate columns, First Name & Last Name. I've combined the two columns into one so it appears Last Name, First Name. Some of the names have middle names. Is there anyway to delete the middle name.
Example: Cope, David Simon
Trying to get: Cope, David
I am using Excel on a work computer that someone else may have changed the settings on.
I want to simply delete a cell, without deleting the entire row the cell belongs to. Yet when I highlight the cell, and go to Edit -> Delete, the program asks me if I want to delete the entire row, and only gives me two options: OK or Cancel. So I can't delete just one cell (and have all of the lower cells shift up one).
When I right click, the 'delete' option is not present either. I tried changing the menu properties to add the 'delete' function, but it will not delete a cell without taking out the whole row.
I have a lot of calculations going on so I thought I would name some of the cells where factors come from to make it easier to follow.
I made a typo and named a cell wrong, is there a way to delete it? Right now I have a correct and incorrect name attached to the cell.
hey everyone
i'm using a table like this one :
A B C D
1 Main | value | value | value
2 Extra | value | value | value
3 Main | value | value | value
.
.
n Extra | value | value | value
i want to delete every cell in column "D" only when cell in column "A" contains "Extra" value.
I want to delete columns that contain a 0 value in row 2 of the column.
In my case, I have headers in row 1 and values in rows 2 and below. I eventually want to filter out the columns that contain no values below the headers.
Let me know if you need any more information for clarification.
I was wondering if ther is a way to delete what is in one cell, once something has been enterted in another?
Example A1 has In-progress written in the cell.
Once B1 has Completed.
So once B1 has been entered, the info in cell A1 will be deleted.
Creating macros in excel. I am looking for a way to delete a row at the active cell. So far this code does the job for me:
Code:
Sub slet()
ActiveSheet.Unprotect
ActiveCell.EntireRow.Delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End Sub
But I want to make it impossible to use this code on the first 4 rows.
I'm looking to do a search and delete in Excel 2007 and I'm having a great deal of difficulty trying to do this. I've attempted to modify some code I found on the internet and not having very good luck with it. Here is the scenario, I've got a spreadsheet with 5 columns (A to E). In column C, there is a product name with certain identifiers that set it apart. An example of this product name with identifier is "product XXX_type 2_attribute ". I want to search for "type 2" in Column C of each row and then delete the row.
Here is what I have written so far and I'm not having any luck.
Sub RowDel()
Dim cell As Range
For Each cell In Range(Range("c4"), _
Range("c65536").End(xlUp))
If cell = "_GPnl_" Then
Range(cell, _
Cells(Rows.Count, 1)).EntireRow.Delete
Exit For
End If
Next cell
End Sub
I want to delete part of a cell in Excel. For example if a cell had the data "BlueGreen" how would I delete the "Blue" bit just to leave the "Green" bit behind? Surely there is an easier way then physically deleting the first part in each cell? I have about 4000 cells to work on!
View 9 Replies View RelatedI have a pretty good sized Database and all the rows that are indented are not needed. Anyone know a way to delete all the text within a cell that is indented?
View 9 Replies View RelatedWhat I am trying to do is go through a large form and remove everything except for a certain set of cells, I am using this code
For i = 10000 To 1 Step -1
Set rng = Range("A" & i)
If rng.Value <> "Employee" Or rng.value <> " Total" Then
rng.EntireRow.Delete
End If
Next i
What I want it to do is go through the sheet and delete anything that doesnt have Employee or Total in column A, but my problem is that they dont JUST say Employee or Total, its more like Employee: Team A or Employee Group 1. The Total part always says "Total of " and a number, but the number is always different. How do I make it not delete it if it has Employee or Total anywhere in the cell?
I am running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).