Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

Vba - compare two sheets [Closed]

Report
-
 supereliseo -
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

Answer 1 / 4
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 797
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
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 4

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 ;)
Answer 3 / 4
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 797
not able to think any simpler way at present. sorry.

if it is more than 20000 rows better use access
Answer 4 / 4

thx again anyway, you saved me... :)
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!