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,081,767
Messages
5,361,164
Members
400,617
Latest member
barron1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top