Delete rows that don't match another data set
Closed
HiDrewsah
-
Jan 27, 2012 at 03:40 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 28, 2012 at 03:00 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 28, 2012 at 03:00 AM
Related:
- Delete rows that don't match another data set
- Delete my whatsapp account without app - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Music match jukebox - Download - Audio playback
- Tmobile data check - Guide
- How to set auto redial on android - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 28, 2012 at 03:00 AM
Jan 28, 2012 at 03:00 AM
Sample data sheet1 is like this from A1
hdng1 hdng2 hdng3
2 x x
3 x x
4 x x
5 x x
6 x x
7 x x
8 x x
9 x x
10 x x
11 x x
12 x x
13 x x
14 x x
15 x x
16 x x
17 x x
18 x x
19 x x
20 x x
Sheet 2 is like this
hdng1
1
3
5
7
9
COPY SHEET1 TO SHEET3 ALSO AS IT IS, FOR RETRIEVAL OR FOR UNDOING
Try this macro "test"
hdng1 hdng2 hdng3
2 x x
3 x x
4 x x
5 x x
6 x x
7 x x
8 x x
9 x x
10 x x
11 x x
12 x x
13 x x
14 x x
15 x x
16 x x
17 x x
18 x x
19 x x
20 x x
Sheet 2 is like this
hdng1
1
3
5
7
9
COPY SHEET1 TO SHEET3 ALSO AS IT IS, FOR RETRIEVAL OR FOR UNDOING
Try this macro "test"
Sub test() Dim r As Range, filt As Range, rsheet2 As Range undo With Worksheets("sheet1") Set r = .Range("A2") End With With Worksheets("sheet2") Set rsheet2 = Range(.Range("A2"), .Range("A2").End(xlDown)) End With Worksheets("sheet1").Activate Do If r = "" Then Exit Do If WorksheetFunction.CountIf(rsheet2, r.Value) Then r.Offset(0, 3) = "remove" Else r.Offset(0, 3) = "keep" End If Set r = r.Offset(1, 0) Loop Set r = Range("a1").CurrentRegion r.AutoFilter field:=4, Criteria1:="remove" Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible) filt.EntireRow.Delete ActiveSheet.AutoFilterMode = False Range("D1").EntireColumn.Delete End Sub
Sub undo() Worksheets("sheet1").Cells.Clear Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("a1") End Sub