Delete Rows Same As Rows On Another Worksheet
Nov 17, 2007
I have a workbook where I import data from another workbook. This might get done several times a month. As I go through the imported data I delete rows not needed which gets place into another worksheet. What I need is when I import data again is a macro that the worksheet where my already deleted rows are and compairs them to the new data imported and if the first 13 columns of data matches it delest them off the imported sheet.
I found some code which I altered at [url]
and posted the same question but haven't got any response as of yet.
So the code that i go works but only if the row matches in the exact order on both sheets so e.g. (if row 1 on sheet1 matches row 1 on sheet2) it works but if (row1 on sheet1 matches row 3 on sheet2) it does not.
Here's the code I have so far.
Public Sub delOLD()
Dim i As Long
Dim iLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets("Data") ' Imported Data
Set ws1 = Sheets("Old Records") ' Deleted Data
'ws1.Visible = xlSheetVisible
View 4 Replies
ADVERTISEMENT
Nov 15, 2008
I have anywhere from 3000 to 10000 rows to delete in a a number of worksheets, but would like to avoid using a loop as even with onscreen update turned to off like this:
View 3 Replies
View Related
Mar 8, 2009
I've added a button with a macro to delete any blank rows in a worksheet.
Cell A3 is completely blank and A6 has an IF formula which returns "". In my macro it copies the whole worksheet and then paste special values, to take away the formula in A6 and then deletes the blank rows but it still does not delete row 6.
View 4 Replies
View Related
Apr 4, 2007
Is it possible to delete multiple rows when the worksheet is protected using vba? For Example Peps - Select a undetermined amount of rows and then use a macro to unprotect - delete the selected rows - protect.
View 5 Replies
View Related
Apr 5, 2014
I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???
I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.
View 2 Replies
View Related
May 9, 2014
Please see this attached image: HowTo Do This.PNG
I have this worksheet that I did not create and notice it has entire rows and columns completely gone / deleted. For example, the only columns it has ranges from A to AI and then no more columns exist in the worksheet. For the rows, it has rows 1 through 77 but then it shows a gray background.
How did they do this? What is this called? How can I do this to Worksheets?
View 4 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Apr 16, 2014
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
View 5 Replies
View Related
Apr 3, 2014
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
View 14 Replies
View Related
Nov 15, 2013
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub
View 3 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
[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
May 10, 2012
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
View 1 Replies
View Related
Sep 30, 2008
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.
View 9 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 21, 2008
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.
View 9 Replies
View Related
Nov 5, 2006
Need to solve my problem in the thread "Type Mismatch Error Message". Now a new problem has come up in the same code, so - according to the rules - I've started a new thread. (This one is most likely due to my poor knowledge of VBA syntax).
Sub Delete_invalid_rows()
Dim i%, j%
Dim Nr%, valid As Boolean, BYPdata As Boolean
Dim ar1 As Variant
Dim ar2 As Variant
Dim ar3 As Variant
Dim ar4 As Variant
Nr = 20
ar1 = Array(11, 14, 19, _
20, 22, 25, 26, 27, 28, 29, _
30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, _ .................
View 2 Replies
View Related
Mar 5, 2008
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
End If
Next Row_Counter
End Sub
View 9 Replies
View Related
Jun 20, 2008
I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.
View 2 Replies
View Related
May 9, 2014
I would like to write a macro button, in order to lookup through col. "D" and where find same invoice number (invoice number combine with 3 letters plus dash), should sum the corresponding rows' amounts in col."J" into one row and the other row should be deleted.
View 3 Replies
View Related
Jun 28, 2007
I have a sheet as shown in the picture below. If the cells in Column N has some values that are the result of some calculations. How ever if there are more than one of it, the entire row has to be deleted.
Eg:The value in Column N 816.323 has 3 rows below each other.Only one has to be there.There can be upto 10 values one after other in this way.
I tried to write a progrma by using loops and cehcking if ther is value in the cell,go to cell below and if there is value,delete the row in a loop of 10 times.But it didnt work
View 6 Replies
View Related
Jul 15, 2009
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 )
View 3 Replies
View Related
Jan 3, 2010
The number of rows you are pasting and then you can select that same number of existing rows and use "insert". This is what I'm trying to achieve:
(before paste)
A
B
C
(after pasting unknown number of rows)
1
2
...
n
A
B
C
Is there really no built-in way to do this?
View 6 Replies
View Related
May 22, 2008
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies
View Related
May 3, 2013
coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.
As a simplified example:
Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!
So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.
I was thinking of using something like this:
Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If
I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.
View 5 Replies
View Related
Jan 28, 2010
I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.
This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.
I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.
I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.
View 12 Replies
View Related
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
Nov 26, 2012
How do I populate a second worksheet with only the preselected highlighted rows of cells from another worksheet.
This way I will end up with only the selected data from the first worksheet in the second worksheet.
View 9 Replies
View Related
Jun 22, 2006
I would like to be able to select several non-sequential rows in a worksheet called "Data" (using a check box or just entering a value in Column A) and then be able to press a Command button to copy the selected rows to another worksheet called "Estimate" at the bottom of a table, and delete the designators in Column A (i.e. deletes the value, or unchecks the boxes) so I can repeat the process again if needed.
View 7 Replies
View Related
May 27, 2014
I would like a macro to look into the values of column H11 to BG200 and if ALL the cells in the row contains zero, delete the entire row. see attached a short example for clarification.
View 9 Replies
View Related