Hi all
I wrote a macro that compares the columns B, which contains file numbers, in two worksheets. There are three possibilities: the file number exists in both columns, the file number exists only in the first column and the file number exists only in the second column. If e.g. the file number exists in both columns, the macro should copy/paste the entire row to another sheet. Same for the other two scenario's. Both columns consist of about 90 000 cells. It works great for a small number of rows (around 1000), but for 90 000 rows, it doesn't work. It takes a long time to run and at the end it just crashes.
At another forum, someone pointed out that I'm doing 90 000 * 90 000 * 2 loops, which is way too much, but I don't know how to code it differently. Please note that I posted this on other fora as well, but I didn't receive a solution yet. If so, I will post it immediately.
I can't add a sample file, because it is too big. If anyone would like a sample file, give me your mail address and I will send it to you.
Thanks!
I wrote a macro that compares the columns B, which contains file numbers, in two worksheets. There are three possibilities: the file number exists in both columns, the file number exists only in the first column and the file number exists only in the second column. If e.g. the file number exists in both columns, the macro should copy/paste the entire row to another sheet. Same for the other two scenario's. Both columns consist of about 90 000 cells. It works great for a small number of rows (around 1000), but for 90 000 rows, it doesn't work. It takes a long time to run and at the end it just crashes.
At another forum, someone pointed out that I'm doing 90 000 * 90 000 * 2 loops, which is way too much, but I don't know how to code it differently. Please note that I posted this on other fora as well, but I didn't receive a solution yet. If so, I will post it immediately.
I can't add a sample file, because it is too big. If anyone would like a sample file, give me your mail address and I will send it to you.
Thanks!
Code:
Option Explicit
Sub Compare()
Dim DataDec As Worksheet, DataJune As Worksheet
Dim lastRowDec As Long
Dim lastRowJune As Long
Dim lastRowPresAbs As Long
Dim lastRowPresPres As Long
Dim lastRowAbsPres As Long
Dim foundTrue As Boolean
Dim i As Long, j As Long, k As Long, l As Long
'Define the last row of the different sheets
lastRowDec = Sheets("DataDec").Cells(Sheets("DataDec").Rows.Count, "B").End(xlUp).Row
lastRowJune = Sheets("DataJune").Cells(Sheets ("DataJune").Rows.Count, "B").End(xlUp).Row
lastRowPresAbs = Sheets("PresAbs").Cells(Sheets("PresAbs").Rows.Count, "B").End(xlUp).Row
lastRowPresPres = Sheets("PresPres").Cells(Sheets ("PresPres").Rows.Count, "B").End(xlUp).Row
lastRowAbsPres = Sheets("AbsPres").Cells(Sheets("AbsPres").Rows.Count, "B").End(xlUp).Row
'Compare the file numbers in column B of both sheets. If they are the same, copy/paste the entire row to sheet PresPres,
'if they are not, copy/paste the entire row to sheet PresAbs.
For i = 1To lastRowDec
foundTrue = False
For j = 1To lastRowJune
If Sheets("DataDec").Cells(i, 2).Value = Sheets("DataJune").Cells(j, 2).Value Then
foundTrue = True
Sheets("PresPres").Rows(lastRowPresPres + 1) = Sheets("DataDec").Rows(i)
lastRowPresPres = lastRowPresPres + 1
ExitFor
EndIf
Next j
IfNot foundTrue Then
Sheets("DataDec").Rows(i).Copy Destination:= _
Sheets("PresAbs").Rows(lastRowPresAbs + 1)
lastRowPresAbs = lastRowPresAbs + 1
EndIf
Next i
'Look if there are file numbers that are only present in June's database. If so, copy/paste entire row to sheet AbsPres.
For k = 1To lastRowJune
foundTrue = False
For l = 1To lastRowDec
If Sheets("DataJune").Cells(k, 2).Value = Sheets("DataDec").Cells(l, 2).Value Then
foundTrue = True
ExitFor
EndIf
Next l
IfNot foundTrue Then
Sheets("DataJune").Rows(k).Copy Destination:= _
Sheets("AbsPres").Rows(lastRowAbsPres + 1)
lastRowAbsPres = lastRowAbsPres + 1
EndIf
Next k
'Stop screen from updating to speed things up.
Application.ScreenUpdating = True
Application.EnableEvents = True
EndSub