VBA compare 2 ranges and return values which occur in one and not the other

danno79

New Member
Joined
Oct 28, 2009
Messages
45
I have 2 ranges "SheetNames" starting in cell D7 - generated by the code below, and "OrgNames" starting in cell E7, that is input by a user . I want to generate a list starting in cell F7 containing values which are in OrgNames but not in SheetNames. There will always be values in SheetNames that do not occur in OrgNames. I'm sure this is probably quite simple to achieve but i'm stumped- any suggestions?....

Code:
Sub find_sheet_names()

Dim x As Integer
For x = 1 To Worksheets.Count
Cells(x + 7, 4).Value = Worksheets(x).Name
Next x

End Sub
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
a formula method would be to do a vlookup and wherever there are #N/A are the unmatched
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This will list Unique items in column D to column F:
Code:
Sub CopyUnique2ColF()
'Lists duplicate items between columns D and E to F
    Range("F7:F65536").ClearContents
    er = 7
    For i = 7 To Range("D65536").End(xlUp).Row
         If Application.WorksheetFunction.CountIf(Range("E:E"), Range("D" & i)) = 0 Then
              Range("F" & er) = Range("D" & i)
              er = er + 1
         End If
    Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top