Related:
- Delete rows that don't match another data set
- Sed delete row - How-To - Sed
- Excel vba copy row if cell value matches ✓ - Forum - Office Software
- Excel - A macro for deleting row on condition - How-To - Excel
- Visual Basic - Deleting rows in spreadsheet - How-To - Excel
- Delete row in word - How-To - Word
1 reply
venkat1926
- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015
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
Subscribe To Our Newsletter!
