Vba - compare two sheets

Closed
supereliseo - Jan 6, 2012 at 02:30 PM
 supereliseo - Jan 8, 2012 at 03:25 PM
Hello everybody,

I have two sheets:

- sheet1 holding 3000 entries of all the guys
- sheet2 holding the transferred guys

i need a macro that automatically puts on sheet3 al the NOT transferred guys

the idea is the following:
{not transferred guys} = {all the guys} - {transferred guys}

the range of the guys on each sheet starts from A14 and has 4 columns
Each row is unique, not each cell, for example 2 different guys can have the same surname.

thnaks in advance and sorry for my engish (I'm from Italy)

4 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 6, 2012 at 10:36 PM
sheet 1 is like this
name data
a 1
s 2
d 3
f 4
g 5
h 6
j 7
k 8
l 9
sheet 2 is
name data
a 1
d 3
g 5
j 7
l 9

COPY SHEET 1 IN SHEET3 FROM a1

now try this macro test

Sub test()
Dim c() As String, j As Long, k As Long, cfind As Range
With Worksheets("sheet2")
j = WorksheetFunction.CountA(.Columns("A:A")) - 1
ReDim c(1 To j)
For k = 1 To j
 c(k) = .Cells(k + 1, 1).Value
Next k
With Worksheets("sheet1")
For k = 1 To j
Set cfind = .Cells.Find(what:=c(k), lookat:=xlWhole)
If Not cfind Is Nothing Then
'cfind.Select
cfind.EntireRow.Delete
End If
Next k
End With
End With
End Sub



Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("A1")


End Sub
2
Hi Venkat 1926

your help is very good, thx a lot for your kindly cooperation.

Just one thing, to improve the speed I did:

Application.ScreenUpdating = False

'YOUR CODE

Application.ScreenUpdating = True

Can you suggest me something else to speed up the excecution time with thousands of rows?
For me also earning one second is helpful...

thx again Venkat ;)
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 7, 2012 at 11:40 PM
not able to think any simpler way at present. sorry.

if it is more than 20000 rows better use access
0
thx again anyway, you saved me... :)
0