combining ranges on several worksheets without duplicates?

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi,

I am trying to combine the first 2 columns of several ranges on different worksheets but without any duplicates (stacking them, i.e. the resulting range has 2 columns too) and then naming the range.

I am having a lot of trouble with this! I tried several methods, one of which has 2 For loops which deletes a row when a duplicate is found, but I keep getting error messages (something about arrays...)! Please help! I was also putting the ranges together in a primitive way by re-importing the first 2 columns from the original data text files... a more sophisticated way would be much appreciated :LOL:

Thanks!
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello Barq:
Duplicates from the list can be removed by using the "copy unique" option of the "Advanced Filter " ... would that help ?
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi,

not really, since I would like to automate this. Basically, this combined ranged will be used to populated a combo box. Right now, the data is imported automatically everyday, so I need the combo box to be updated too.

Thanks.
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello Barq
If you could provide more detail I may be able to help you with some code.

What is the name of the target Combo box ?
Is the Combo Box in a userform or on a sheet ?
What is the source of the data ?
Is the source data in a particular column of a particular sheet ? If so what sheet and what column ?
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi Nimrod,

Right now, I have 9 worksheets that imports data from 9 different .csv files. The first columns of each worksheet are IDs and the second, names. What I want to do is to combined the first 2 columns from each of the 9 sheets onto a new worksheet without duplicates every time any of imported data on the 9 worksheet are refreshed.

I already wrote some code for a combo box that will be filled by a named range from a worksheet.

Thanks for your help!
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
The following code creates a target sheet then it goes to ALL the sheets in the workbook to copy
uniques entries from columns A:B. The copy process is achieved thru Advanced Filter.

Code:
Public Sub ExcludeDupesFromList()
Dim TargSh As Worksheet
Dim SrcSh As Worksheet
Dim TargRng As Range

' MAKE TARGET SHEET
Sheets.Add
Set TargSh = ActiveSheet

' COPY ONLY UNIQUE FROM EACH SOURCE SHEET
For Each SrcSh In Worksheets
    If Not SrcSh.Name = TargSh.Name Then
    Set TargRng = TargSh.Range("A" & TargSh.Cells(65536, 1).End(xlUp).Row + 1)
    Call CopyUnique(SrcSh.Name, "A:B", TargSh.Name, TargRng.Address)
    End If
Next SrcSh

' REMOVE DUPES FROM TARG SHEET
Call CopyUnique(TargSh.Name, "A:B", TargSh.Name, "C1")
TargSh.Columns("A:B").Delete

End Sub

Private Sub CopyUnique(SrcShName, SrcCols, TargSh, TargRng)
On Error GoTo TheEnd

'ADVANCED FILTER UNIQUE COPY
    Sheets(SrcShName).Columns(SrcCols).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Sheets(TargSh).Range(TargRng), _
    Unique:=True
    
TheEnd:
End Sub
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Thanks! I'll give it a try today.
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi Nimrod,
I tried the code, it worked great up till the part where it tried to filter and copy Columns A and B to Column C. I tried to step through the code, what I found was that nothing was being copied to column C at all....

Yikes..
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi Again Nimrod,

I got the code to work by adding a line to delete the two blank cells in the first line. Don't really know why it would not work with the blank cells, but now it works! Thanks a bunch.
 

Forum statistics

Threads
1,077,834
Messages
5,336,661
Members
399,095
Latest member
globz

Some videos you may like

This Week's Hot Topics

Top