I need a macro to sort through a large table of data entries and remove all repetitive entries (entires are based on a number in column A with data entries in B through EY) so i need to locate repetive numbers select entire row and paste them in a new location.
But i cant sort the data as it will change the entire order and i need the data intact. the user will specify which entry number to search for.
I know some sort of search and loop function would work but i just cant seem to make them work.
Excel Range A1 - D500. Data located in column C. I would like to delete the rows in this spread sheet that do not contain any of the following 3 prefixes of part numbers from column C: X-SXP, X-SCM and X-SBP. There are many part numbers but only parts with these 3 prefixes are needed. Part numbers are 10 characters long after the prefix. If any additional information is needed, let me know.
I viewed the Delete Rows Meeting Conditions thread, but the input value in that code is for what you want to delete, and I need to delete everything not meeting the data I want to keep, which is about 30 different part numbers. Also, that appeared to only offer one input and not 3.
I have an extremely large CSV file that I am opening via Excel. Can anyone tell me how to delete a row using multiple criteria. For Example, i want to delete the whole row if a certain cell doesnt equal GA SC or NC. I have seen how it can be done using one criteria but not multiple ones.
I have searched for this topic, and I have read http://www.ozgrid.com/VBA/row-delete-criteria.htm, I don't really understand it though. I think what I want is simple, I want to search column A for cells containing the word " Total" - if any are found I then want to delete the corresponding rows.
i created a work sheet with the help of the wonderful people on this forum. my boss loved it! one problem they now want it to automaticly take out a date and time of the cells when the date rolls around again.
12/25/05 late 00:20:06 when 12/25/06 rolls around they want the system to automaticly see it and delete it out with the amount of time they were late or sick ect.... or if the sup was out that day and comes in the next day the system will see that the date has past and will up date the info. the other catch is i need it to move everything up one space when it deletes somthing. i want it to read j18 and k18 as one and so through j and k 40 same for l and m n and o ect.... i attached the sheet
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
I have a worksheet that contains 2 Rn managers per 3 Site and 2 TSR managers per 3 Site. I want to be able to sort these managers by Site into 6 tabs: 3 RN tabs and 3 TSR tabs. I want to keep the original worksheet as is and copy the appropriate rows to the designated worksheets labeled SACRN SACTSR VALRN VALTSR and so on. The data range is A:M
The columns of value is A (JOB) and B (SITE) and here is the sample of the data
JOB SITE TEAM RN SITE SAC GASTON SUSAN TEAM SAC BERNAL RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO RN SITE VAL RANADA TERESA TEAM VAL RINCON RN SITE VAL RANADA TERESA TEAM VAL RINCON RN SITE VAL RANADA TERESA TEAM VAL RINCON RN SITE SAC MCINNIS JOHN TEAM SAC SMITH RN SITE SAC MCINNIS JOHN TEAM SAC LEMON RN SITE SAC MCINNIS JOHN TEAM SAC JACK TSR SITE SAC GRECO DAVE M TEAM SAC KEN TSR SITE SAC GRECO DAVE M TEAM SAC ROGERS TSR SITE SAC GRECO DAVE M TEAM SAC LOPEZ TSR SITE SAC SHAVER KELLY TEAM SAC JONES TSR SITE VAL FRANCIS TEAM SAC YANG
In my code I am searching a spreadsheet for certain states and deleting rows that have states I want to exclude. Is there a way to do this with a list of states, instead of having to make many for loops?
For i = lastrow To 2 Step -1 Cells(i, 4).Select If Cells(i, 4).Value = "PA" Then Rows(i).Select Selection.ClearContents End If On Error Resume Next Next For i = lastrow To 2 Step -1 Cells(i, 4).Select If Cells(i, 4).Value = "TX" Then Rows(i).Select Selection.ClearContents End If On Error Resume Next Next
I have the following code that works well on a single worksheet
Sub hide() For Each rw In ActiveSheet.UsedRange.Rows If rw. Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True Next rw End Sub
However I want it to run on each worksheet in the workbook when I run the macro. I tried this but it seems to loop continuously on the first worksheet. When I press escape the line "next row" is highlighted in yellow by the debug in the VBA Editor.
Sub hide() Dim ws As Worksheet Dim rw For Each ws In ThisWorkbook.Worksheets
For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True Next rw
I'm trying to make my spreadsheet to show next hidden row by entering a text or number on the cells in the range of my code. It works from rows 67 to 135 but I canot make it work with an additional ranges on it. if i insert the same instruction 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' it shows an error that said ambiguos name detected.?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If RenglonAnt > 67 And RenglonAnt < 135 Then If Range("C" & RenglonAnt).Value <> "" Then Sheet1.Rows(RenglonAnt + 1).Hidden = False
End If End If If Target.Row > 67 And Target.Row < 135 Then RenglonAnt = Target.Row
I have a macro that hides rows in my workbook if certain criterias are met. I'd like to add a criteria to the logic that only hides rows if the date in cell 17 is less than 3 months from today's date. The date in cell 17 is shown in this format: 25-03-2008.
Sub ForceHide() Dim ws As Worksheet Dim line As Integer Dim Endline As Long For Each ws In Worksheets If ws.Name <> " Total" And ws.Name <> "Batch" And ws.Name <> "Summary" And ws.Name <> "PivotTable" And ws.Name <> "Fields" And ws.Name <> "RTP" Then With ws........................
I have a large file of invoices, and some have been paid. The problem is, sometimes it is not paid against the correct invoice, so I have a lot of credit and debit that should offset each other. I want to go through the sheet and highlight all the rows that has a matching ID and an a 0 value when total amount column was added up to another row. I would like it so that it highlights a different color for every pair of offsetting amount, but the same color highlight is fine too.
1) I have a original worhsheet_A which with header rows(1~6) and many columns. 2) In the sheet_A, there are 2 columns (ColumnN & S) which I need to set criteria (value>5). 3) If any row's data which meet the criteria (value >5) with either column N or S, copy all the entired rows to sheet_B following sheet_A sequence. 4) The sheet_B should have the same header row with sheet_A(row1~6).
Btw, is possible create a corn job trigger this macro to run daily 9am to send out the worksheet_B as email, so that the command button can be disregarded.
I would like to setup a macro that brings up a dialog box asking for a date in a specific column. Once that date is entered in the dialog box, the macro would then find all rows before that date (in that specfic column) and delete them.
My worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
I'm working with transcribed comments from patient satisfaction surveys. The goal is to produce a single Excel file that contains all of the positive comments from source files where physicians are mentioned by name. Our vendor notes this by preceding any person's name with an asterisk (e.g., *Dr. Smith or *Doctor Smith"). I've attached a sample source file (Ambulatory.xls), so you can see what I'm starting with (I've deleted the identifying data in columns B, C, and D).
I'm looking for the appropriate VBA code to cut and paste the row in one file (in this case, Ambulatory.xls) to a separate Excel file (Weekly Physician Comment Report.xls) IF the cell contents in column G of that row contain either of the following text pieces:
Each week's file will obviously have an unknown number of total rows - and will contain lots of positive comments that DON'T mention a physician by name. In the file I've attached here, for example, there is only one such comment (highlighted in yellow). I simply want to cut and paste the rows with physician names into the new file. If you can provide me with the code to do this for this example file, I can repeat the process for my other files (there are 6 source files in all).
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run. 2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L 3. In Column D, I want to specify 3 criterias namely A, B and C 4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo 5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341. Sub DeleteRows() Column_To_Check = 1 Start_Row = 1 End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row MsgBox End_Row Search_String = "SA341" For Row_Counter = End_Row To Start_Row Step -1 If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then ActiveSheet.Rows(Row_Counter).Delete
delete rows if two conditions are met .THe sample data looks as shown below:
I need to check for a given resource where the allocation hours are highest against which project , and then delete the rest of the rows for that resource records.If the Allocation hours are the same , then the priority column should be considered the highest priority should be included and the rest to be deleted.And if the priority is also the same then need just one record to remain rest of the rows to be deleted.I have priority of business area ranging from
I have the following table, which starts in Column A, row 2. I would like to have a macro, that deletes some rows after certain criteria. If Column F = SO normal and Column E = aa, bb, cc, dd, ee, ff then delete the whole row.
I have an excel 2007 file, which is quite big: ~10mb. There are situated plenty of columns with coordinates. I would like to transcribe it, modify those data, but it will take me ages. I'm not acquainted with macros, but I heard that people can do with them everything using computers' abilities. I'll abridge my whole problem.
I give you a sample of that file, so please open it. In the file you can see two 'columns'. Every column has two other columns called x and y. As you are thinking now these two columns are containing arguments (in x column) and their values (in column y). Every third column is empty. In attached file you see, that arguments are repeating and values are increasing by one. Sometimes there is an argument without repeats, and sometimes there are only two repeating arguments.
What I wanted to do is delete those repeating arguments leaving arguments with the highest and the lowest value. Example: Please look at range A230:B232. There are three repeating arguments and for this case I should delete row 231 from columns A&B and then move cells up to join separated columns. There are also not repeating arguments, which I need to double. If there will be only two repeating arguments I would like leave them. In the end I should have a clear view for all those coordinates.
i have several student ID results that have conditional formatting in place. I'd like to KEEP the following data but delete/hide everything else:
1) Filter out purple rows 2) WITH filtered rows: KEEP any scoreA that's 100% or less than 85% 3) Look at Score A and Score B - KEEP if the difference is greater/less than 5% 4) If a student took testB but didnt take testA, KEEP.