Delete Rows Matching Cell, Across All Worksheets
Aug 20, 2006
in VBA, I would like to do the following:
Delete the entire row where any cell in that row is equal to cell "b15" in worksheet "Metal Type".
I would like it to run across all the worksheets hidden or unhidden, in the workbook, (except for worksheet "Metal Type").
If possible I would also like it to prompt the user to accept the contents of cell "b15" in worksheet "Metal Type" or enter a new string.
Also can it alert the user when "no matches were found"
View 9 Replies
ADVERTISEMENT
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
View 4 Replies
View Related
Aug 28, 2007
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).
View 2 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
View 2 Replies
View Related
Jun 17, 2009
Situation: I would like to compare the information between two worksheets and delete the rows that contain the same data in multiple columns, on a row by row comparison.
IE: I have two worksheets, each have identical row headers, with 5 columns each.
Company Load Date Load Time Load Description Amount ?Report?
Store#44 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
Store#44 5/14/2009 02:34:21 AM SLATOUR $20.00 WS1
Store#45 5/14/2009 01:55:41 AM GCHANDLER $100.00 WS1
Store#46 5/14/2009 11:55:41 AM MMBAYO $40.00 WS1
If column A(Company), B(Load Date), and E(Amount) for record 35 in worksheet one, match the same columns for a record in worksheet two, both records are deleted/highlighted/marked with an x in an additional column/anything.
Alternately, I can combine the data in both worksheets into one large worksheet, if that would make the solution easier. And or adding a column that idenifies which record came from which report.
Basically I have two similar reports; each contain a few rows of transactions that the other does not, I need to separate the matching transactions from the unique transactions, in order to balance the two.
I have tried using the Remove Duplicates function but it saves one of the matching records (they should add an opiton to delete matching records aswell keeping only truly unique records), I dont understand how to work Conditional Formatting to get it to do what I want, I dont know macros, or vlookups.
View 9 Replies
View Related
May 11, 2007
I have been using the code found here
Sub DeleteRowsFastest()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else.............................
to delete rows that match the given criteria. I am now wanting to do the opposite, keep the rows matching my given criteria and delete all others.
View 3 Replies
View Related
Jul 8, 2009
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.
View 7 Replies
View Related
Sep 12, 2008
I have a worksheet with 20,000 plus rows, header=yes, and I need to delete all rows NOT containing the numbers 6600, 6700 or 6800 in column C. I'm currently using the below code which works, but is DOG slow.
Sub TestDeleteRows()
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
View 5 Replies
View Related
Mar 10, 2008
I have a need to call a sheet from another sheet, copy two pages from the newly opened, existing sheet to a new workbook, search one sheet of the new workbook for any entries that don't contain a variable I specify, which is the first four digits of the field, and delete them. The sheets represent a daily view of jobs (identified by the "RptDate" variable) and an entire monthly list of jobs, which I want to delete all of them not from the date specified in the variable "RptDate". The fields in column A contain the date in "mmdd" format as the first four digits, and I have that specified in the "B1" celll of the originating workbook.
I found some code in this link Search Column Delete Row If Value Found that looked good. I had to modify it since I needed to search for a variable (RptDate) and needed to search column A and start on row 8 of that column. My current code looks like this:
Sub Macro1()
Dim RptDate As String
Dim RptMonth As String
Dim iLastRow As Long
Dim i As Long
RptDate = Range("B1").Text
RptMonth = Range("B2").Text
Workbooks.Open Filename:=(RptMonth)
Sheets( Array(RptDate, "Total Database")).Copy
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 8 Step -1
If Left(Cells(i, "A").Value, 4) <> RptDate Then
Rows(i).Delete
End If
Next i
End Sub
It works fine right up to the "Rows(i).Delete" line. It errors out with a "run-time error 1004, delete method of range class failed" and highlights the above line. I know the fields I'm pulling the variables from are working right since it opens the correct workbook off of one of them.
View 6 Replies
View Related
Feb 5, 2009
I've got two workbooks, Workbook1 with a list in column a and Workbook2 with a list in column F. I want to compare the cells in these columns and delete the entire row in Workbook2 if there is a match.
View 3 Replies
View Related
Apr 20, 2009
Hi i have the below code which runs on deleting duplicate code which i found in an excel manual. I am having some trouble as i want to convert the code so that it will run on all worksheets i have which are numbered such as 1,2,3,4 etc.
This is so it makes the workbook easy maintenance for when deleting or adding worksheets which happens quite regularly.
View 7 Replies
View Related
Jun 24, 2009
I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.
View 4 Replies
View Related
Jul 1, 2014
I have a workbook has many worksheets, I would like to be able to delete worksheets if, for example, the number of rows in the worksheet is less than 100 rows.
View 3 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
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
Jun 24, 2008
I had a quick look around and couldn't find exactly what I was after my apologies if it already exists. I have attached a sample worksheet and I would like it the entire row A from sheet 1 to sheet 3 if the cells A2 OR D2 contain what is in cell Sheet2!A1.
View 3 Replies
View Related
May 1, 2008
I am trying to select all cells in a range whos contents match the contents of a specific cell so I can group the respective rows of these cells. I did a forum search but I am not sure if I have found what I am looking to do. I am a very basic user of VB so keep answers as simple as possible.
View 8 Replies
View Related
May 21, 2008
I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".
Sub DeleteFirstRowInWorksheet()
Dim SheetName As Worksheet
Dim i As Integer
For Each SheetName In Sheets
If Range("A1") = "table" Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If
Next SheetName
End Sub
It delete only in an active sheet. What's wrong?
View 5 Replies
View Related
Jan 16, 2009
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
View 2 Replies
View Related
Jun 10, 2014
I do have a workbook has many worksheets. I would like to be able to match (or search) for first name and / or surname between one worksheet (Sheet,1, which has all the names I would like to match with the other worksheets) and the other sheets in the same workbook (the data is very sensitive). Is the possible way of doing this using VBA.
View 14 Replies
View Related
Mar 2, 2013
I have two worksheets. Each have a common value in two cells. An ID and a currency amount. One worksheet also has a payment date. I need a worksheet that combines the two, along with the payment date.
View 3 Replies
View Related
Jan 24, 2013
I need to sort out the data. I have in one worksheet data arranged as
A1 B1 C1
1
2
3
4
5
6
7
8
.
.
.
In second worksheet it is arranged as
A1 B1 C1
2
3
5
8
12
15
.
.
.
I want to merge the content from first worksheet into the matching and correponding column no of second worksheet
View 7 Replies
View Related
Jun 5, 2014
I am trying to compare deductions from two different pay periods. I have the Data from check 1 & check 2 on different worksheets, in no order, and would like the data to auto populate on a third worksheet into a side-by-side comparison of the data. The problem comes when there is a deduction missed or a new deduction that only shows up on one of the two checks. I am having to manually insert lines to make my side-by-side comparison match. Is there a macro or something that can pull the data from these two soures and display them in a side-by-side comparison even if there is no match?
View 1 Replies
View Related
Sep 7, 2008
I have two worksheets with one row of 'accounting' type numbers in A1:A100 (it can be shorter or longer) on both sheets. I need to run a search on worksheet one to see which of them exactly match any on the second worksheet and then highlight them.
It can be a macro or it can highlight each entry a different color. Something so that I can determine which two numbers go together and I can run the thing only once.
There most likely will not be multiples on the same worksheet but if there are it will be few. If multiples happen it is ok it just needs to be matched to the second worksheet in a different color than the other similar numbers.
Another thing is if there is no match in either worksheet that is ok it can just skip to the next number in the list and check that one. There does not need to be a match.
View 9 Replies
View Related
Jul 25, 2007
I have a workbook with 3 sheets. AP, Move, Match. AP & Move will be about 40,000 lines.
I need a macro that will take the data from those two sheets and match them up on the match sheet by column A Line by line with all data moving over. An example is attached.
I'm not sure if this is possible, but please let me know if you need more info because I really don't want to do this manually. Anything that doesn't match I just need a blank line. I can add the true-false formula, but if it would fit in the macro that would be cool to.
View 7 Replies
View Related
Jan 28, 2010
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
View 9 Replies
View Related
Nov 19, 2009
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
View 12 Replies
View Related
Jan 12, 2010
writing a macro script. I want to find matching names on two diffferent worksheets. More specifically I need the ability to match if I apply a abbreviation(s) to worksheet one in order to get matches in worksheet two.
e.g Company = Co. or
Insurance = Ins
Please see my attached excel document for an example.
I have over 27000 lines so doing this manually will take forever!
Ideally I would like this to be a macro script that I can edit to add as many different abbreviations as required. The attached document will make my task a lot clear.
Unfortunately I don't have any starting code as I am not an excel/visual basic programmer, But i hope somebody will be able to help me out here.
The reason I need to do it this way is because I have been given a file from a partner company that uses different abbreviations to the ones we use in my company. I want to see if the companies in the list I have been given already exist in out company database, therefore we need a suitable matching procedure.
View 10 Replies
View Related
Feb 24, 2010
I have rankings from several different sources all with different rankings for different people with a different number of people in each set of rankings.
I would like to match the names from each ranking set and place the corresponding ranking into one worksheet. I have done this without the code but over the course of the next couple of months the rankings will be changing.
I have them importing from the web so when they update I would also like for my rankings to update. Attached is the rankings each in their own worksheet.
The final sheet is what the end product looks like.
View 4 Replies
View Related
May 18, 2006
I've got 2 worksheets SH1 & SH2( Two different workbooks) Col A is NAME in each Sheet. Need to Match Col A Sheet 2 to Col A Sh1 if they are identical. If identical copy info from Col B, and Col E- G to Sheet 1 Col B and Col H-J. If a Name is in Sh2 but not in Sh1, Copy (using same Column positions: copy info from Col B, and Col E- G to Sheet 1 Col B and Col H-J ) to NEXT AVAILABLE ROW in Sh1 COL A, and highlight it in Some bright color ; Then open a MSG Box telling me how many new names were added.
View 8 Replies
View Related
