Hi all, this is the code i have currently. it is working but takes too long to finish its task.
Dim a As Integer
Dim c As Integer
Dim z as Integer
a = 6
c = 6
z = 0
lastrow = Worksheets("raw_data").Cells(Rows.count, 1).End(xlUp).Row
While a < lastrow - 6
While c < lastrow - 6
'1)check the column C for both sheets
'if both sheets' column C values are not the same, then check column C value of the next row in sheet"raw data"
'2)else if column C values are the same, check the value of column A
'if column A value is the same, go check next row in sheet"raw data", restart to 1st row in sheet"manual selection"
'if both column A and column C values in sheet "raw data" are are not the same for all rows in sheet"manual selection", then add values of that row to sheet"hidden"
If Worksheets("raw_data").Cells(a, 1) = Worksheets("manual selection").Cells(c, 1) Then
'if name is the same, then check cost
If Worksheets("raw_data").Cells(a, 3) = Worksheets("manual selection").Cells(c, 3) Then
'if cost is the same, then check next row in raw data
GoTo lastline:
Else
c = c + 1
End If
GoTo nextrow:
'check next row in budget
Else
'if names not same
c = c + 1
End If
'check next row in budget
nextrow:
Wend
'check if c <1500
'if name & cost are not same, copy to sheet3(hidden)
Worksheets("raw_data").Cells(a, 1).Resize(1, 3).Copy
Worksheets("hidden").Cells(z + 6, 1).PasteSpecial xlPasteValues
Worksheets("hidden").Cells(z + 6, 2).NumberFormat = "#.00"
Worksheets("hidden").Cells(z + 6, 3).NumberFormat = "#.00"
z = z + 1
lastline:
a = a + 1
c = 6
'raw_data - new row
Wend
i tried this code with 500 entries and it took 1-2min to finish comparing. my real work sample has more than 60k entries, it will take ages to finish comparing. so i am thinking whether is there any alternative to make it run faster?
thanks
Bookmarks