Good day everyone,

With the following code, you compare data between two columns in a
different worksheet, and the results are transferred to a third one.

Sub test()

Dim T(), A As Long
Dim Rg As Range, Rg1 As Range
With Worksheets("month")
Set Rg = .Range("B1:B" & .Range("B65536").End(xlUp).Row)
End With


With Worksheets("Data received")
Set Rg1 = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
End With


For Each c In Rg

If Application.CountIf(Rg1, c) = 0 Then
ReDim Preserve T(A)
T(A) = c.Value
A = A + 1
End If
Next
Worksheets("Result").Range("A1").Resize(UBound(T) + 1) = _
Application.Transpose(T)

End Sub

However, I would like to have the entire row to show on the
destination sheet rather than the cell value only.

Anyone would know where I should modify the code ?

Thanks for your time

Denys

Hi Denys

This will do the job for you. Originally you were placing the
outcomeof Rg in Col A of the "Result" sheet. If you copy the entire
Column of the Cells which do not match, the outcome will appear in
Column b of the "Result" sheet. Not sure if this is what you want?

Take Care

Marcus



Sub test()


Dim T(), A As Long
Dim Rg As Range, Rg1 As Range
Dim lw as Long
lw = Sheets("Result").Range("B" & Rows.Count).End(xlUp).Row + 1
With Worksheets("month")
Set Rg = .Range("B1:B" & .Range("B65536").End(xlUp).Row)
End With


With Worksheets("Data received")
Set Rg1 = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
End With


For Each c In Rg

If Application.CountIf(Rg1, c) = 0 Then
ReDim Preserve T(A)
'T(A) = c.Value
A = A + 1
c.EntireRow.Copy Sheets("Result").Range("A" & lw)
lw = lw + 1
End If
Next
'Worksheets("Result").Range("A1").Resize(UBound(T) + 1) = _
Application.Transpose(T)

End Sub

Hi Marcus,

Thank you very much....it's perfect !!!

Wow !!

Have a nice day

Denys

You already have an answer, but for more general Excel comparison you might
be interested in my Excel utility "Workbook Compare". It was conceived as a
platform for regression testing, but can be used for virtually any Excel
comparison needs whatsoever.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility


"Denys" <(E-Mail Removed)> wrote in message
news:4834e4f4-6634-44e7-aa23-(E-Mail Removed)...
> Good day everyone,
>
> With the following code, you compare data between two columns in a
> different worksheet, and the results are transferred to a third one.
>
> Sub test()
>
> Dim T(), A As Long
> Dim Rg As Range, Rg1 As Range
> With Worksheets("month")
> Set Rg = .Range("B1:B" & .Range("B65536").End(xlUp).Row)
> End With
>
>
> With Worksheets("Data received")
> Set Rg1 = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
> End With
>
>
> For Each c In Rg
>
> If Application.CountIf(Rg1, c) = 0 Then
> ReDim Preserve T(A)
> T(A) = c.Value
> A = A + 1
> End If
> Next
> Worksheets("Result").Range("A1").Resize(UBound(T) + 1) = _
> Application.Transpose(T)
>
> End Sub
>
> However, I would like to have the entire row to show on the
> destination sheet rather than the cell value only.
>
> Anyone would know where I should modify the code ?
>
> Thanks for your time
>
> Denys