Comparing two spreadsheets - deleting rows in one
Closed
fanway
Posts
1
Registration date
Saturday October 5, 2013
Status
Member
Last seen
October 5, 2013
-
Oct 5, 2013 at 05:47 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 7, 2013 at 12:10 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 7, 2013 at 12:10 AM
Related:
- Comparing two spreadsheets - deleting rows in one
- How to delete a row in a table in word - Guide
- Deleting snapchat account - Guide
- Compare excel spreadsheets - Guide
- How can i delete my whatsapp account without logging in - Guide
- How to delete history in facebook - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 7, 2013 at 12:10 AM
Oct 7, 2013 at 12:10 AM
download the sample but trivial file from
http://speedy.sh/Ae8Uh/fanway-131007.xlsm
sheet 1 and sheet 2 are data. sheet1 is copied to sheet 3 to preserve configuration
try this macro "test" on this file and cofirm whether it gives what you want. if yes use or modify the macro to suit your data;.. the macro is in vbeditor and also repeated here.
http://speedy.sh/Ae8Uh/fanway-131007.xlsm
sheet 1 and sheet 2 are data. sheet1 is copied to sheet 3 to preserve configuration
try this macro "test" on this file and cofirm whether it gives what you want. if yes use or modify the macro to suit your data;.. the macro is in vbeditor and also repeated here.
Sub test()
Dim r As Range, r2 As Range, c2 As Range, x(), j As Integer, k As Integer
Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("a1")
With Worksheets("sheet1")
Set r = Range("A1").CurrentRegion
With Worksheets("sheet2")
Set r2 = Range(.Range("A2"), .Range("A2").End(xlDown))
j = .Range("a1").End(xlDown).Row - 1
ReDim x(1 To j)
For k = 1 To j
x(k) = .Range("A2").Offset(k - 1, 0)
Next k
End With
For k = 1 To j
r.AutoFilter field:=.Range("A1").Column, Criteria1:=x(k)
r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Next k
.AutoFilterMode = False
End With
End Sub
Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("a1")
End Sub
