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
- Music match jukebox - Download - Audio playback
- Delete my whatsapp account without app - Guide
- Tmobile data check - Guide
- Transfer data from one excel worksheet to another automatically - 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
