vba - compare two sheetsClosed

Question

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)

Configuration: Windows 7 / Firefox 8.0.1

venkat1926 · Accepted Answer

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

supereliseo · Answer

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 ;)

venkat1926 · Answer

not able to think any simpler way at present. sorry.

if it is more than 20000 rows better use access

supereliseo · Answer

thx again anyway, you saved me... :)

Vba - compare two sheets

4 replies

Subscribe To Our Newsletter!