I have a sheet with data only in column A. I want to remove any rows that dont contain the word MAR. All the lines with MAR in start with: CN=GR GG COF MAR ****
macro which would do this as i have over a 100 sheets. and maybe an explanation as i am totally new to this?
I have a file which has some data I want to keep and some data I want to strip out.
The data I want to keep will always have either a date or a string “Overdue” or a string “> 1 year” in the first column, anything else I want to remove.
Example file shown below
23-Apr-0812341234123412341234
30-Apr-0812341234123412341234
19-May-0812341234123412341234
27-May-0812341234123412341234
> 1 year12341234123412341234
Overdue12341234123412341234
Application.ScreenUpdating = False
Range("B:B").Select
For Each c In Selection
If c.Value = Range("A4") Then c.Rows.Interior.ColorIndex = 36
Application.ScreenUpdating = True
I got this code so far that highligts the rows that match but range a4 needs to go down the column to a5, a6, a7....so it only checks the first cell in column a. I need it check all cells. Then i was going to delete the entire row in column B where it was not highlighted.
I’ve got a spreadsheet with approximately 30,000 rows and I would like to create a macro which hides every row which has not the value 12, 14 or 19 in column I. What is the best way to do it? With an if statement?
View 4 Replies View RelatedI have a spreadsheet which contains data for crime incidents occurring in a month. I have created a worksheet which uses "IF" statements to pull in data that occurred within the last 24 hours only. I would like to create a report that can be printed which has a crime category and lists all the incidents for that category below that occurred during the day. The issue I am having is that all "IF" and "LOOKUP" statements are line for line and I want to be able to skip blanks or where it is not the category in the title.
View 4 Replies View RelatedI'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 RelatedI 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
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 ................................
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.
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] .....
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 RelatedEach 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.
how to use a macro to delete rows that have text in them. I have an imported text file that has headings every 10 rows and I need to take out the heading rows
View 9 Replies View RelatedI have a spreedsheet that has both numerical data and text data in column "A". I need to delete all the rows that have text in Column A and keep the rows that have numerical data.
View 5 Replies View RelatedI 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.
Here is what Im trying to do: I have a VERY large excel file that has groups of 4 cells, a blank, then another 4, repeating. Kinda like the following:
ExampleCell1
ExampleCell2
ExampleCell3
UID = example
ExampleCell1
ExampleCell2
ExampleCell3
ExampleCell1
ExampleCell2
ExampleCell3
UID = example
That pattern continues for about 3000 lines. However, if you noticed, some of the "groups" dont have the 4th line "UID = example".
Here is what Im trying to do. I want excel to search for the cells with "UID" and DELETE it plus the 3 rows above. So that only the "groups" without the UID remain.
Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 3000+ lines of text.
I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.
Can anyone help me out in writing the Visual Basic code?
i am importing a large number of text files from a folder into an excel worksheet (into column A) and found the following code from searching the forum.
Sub Import_Text_Files()
Const PATH = "C:Documents and SettingsSilent BazMy DocumentsElectronic Gift Card WorkTestTextFolder"
Dim My_Filenumber As Integer
Dim My_File As String
Dim My_Data As String
My_File = Trim(Dir(PATH))
If My_File = "" Then
MsgBox "No Files found matching " & PATH & My_Extension
Exit Sub
End If.......
I want to delete any/all rows from a worksheet (named UK) which have the word "VAN" in column P. I have tried using a 'For Each... Next statement' in a macro, but always get a debug box "Run Time Error '91' Object variable or With block variable not set". What am I doing wrong? my code follows
Sheets("UK").Select
Dim cell As Range
For Each cell In Worksheets("UK").Range("P10:P200").Cells
Cells. Find(What:="VAN", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
Selection.EntireRow.Delete
Next
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 looking for a macro to delete entire rows based on the a partial text contained in column A. For example, I have a list of names that I need to keep but I need to delete the rows in which column A contain cells starting with the following.
BOOKLET 201, PROMO
LOTTO, PRIZE 2
PBT 2, STW WEEK 4
the numbrs in these cells increment therefore I can not make it a specific to the cell but need to select based on Booklet, Lotto and PBT.
I need a VBA macro that deletes whole rows if the cell in one of the columns does not match one of three texts. For example, in column B, if the text in there isn't "Mary", "Joseph", or " " (empty space), the row must be deleted.
View 4 Replies View RelatedI have data that covers multiple rows that I need to merge into one row.
Header:| InvoiceNumber | Type | Name
Row1: | 1000 | Payor | Doe, John
Row2: | 1000 | Payor | Smith, Mary
Row3:| 1000 | Payee| Jones, Henry
Row4:| 1000 | Payee | Jones, Bob
I need to get those four example rows down to one row such as:
Header | InvoiceNumber | Payor | Payee
Row1: | 1000 | Doe, John & Smith, Mary | Jones, Henry & Jones, Bob
The number of payor/payee can vary between 1 and several. I need some way to loop through and keep appending the names in one cell separated by a "&". And then deleting all the duplicate rows (based on InvoiceNumber).
I have around 50 text files with similar design per attached file. I need to import the text files with criteria below:-
1. include file name
2. let user choose the folder
3. exclude data from "work in process summary" to "work in process cost totals"
4. only have one title in the excel files which all text files is combined "Item, Line ....."
5. If the text files do not have title like "Item, Line, ..." do not import
Is it possible to have all criteria listed above by running a macro?
A.txt
I am having to write a vba code for a survey data captured code online.
I am attaching the code and the vba module that is suupose to do
following things
2) dELETES THE LAST 3 COLUMNS
3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)
4)Inserts new row at top from Q1 TO Q11
5) Sorts data on Q6 (Faculty/Staff)
Before all this the i need to insert blank cells for questions that have not been answered and shift the cells right (condition 1) ....
Using excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"
EX:
05S0128
06S0112
05S2298
S25852
S36963
SA36185
I would only like to keep the last 3 lines.
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
I have a VLOOKUP formula and if it looks and does not find a match it will either put a "0" or a N/A. Is there something I can add to it to leave the cell blank if it does not find a match?
View 9 Replies View RelatedBasically I have a =VLOOKUP(E2,Sheet3!$A$1:$C$7442,2,FALSE) And sometimes it doesnt find the number its looking up, so theres no data to display, so it just shows #N/A is there anyway I can make the =VLOOKUP(E2,Sheet3!$A$1:$C$7442,2,FALSE) display "not found" if it doesnt find anything?
also sometimes if it does find the number its looking up, and the cell next to it is blank, it just displays 0. I could filter it.. and then just replace all the 0 and #N/A with not found, but im just wondering if i could do it in the code too.
I've developed a VBA application for excel in my english version. Now I sent to the client, wich is dutch and use dutch office. they say it gives errors at simple lines like TextBox1.Left=100. It seems there is no Left property. I installed their office but Left is there for me. I suspect their configuration have some version of VBA with translated code language... like using Links instead Left... wich is same thing for dutch. What could cause VBA translation of properties so I can have same configuration as they do?
View 4 Replies View Related