remove duplicates in named range

Question

i have a problem which i believe you will find a simple solution i am over my head with this one

I have code that copies a range from another workbook, pastes it in the current workbook, and then removes the duplicates

Problem is, after removing the duplicates, it thinks the range is still as long as before removing the duplicates (it includes the blank spaces)

example:

allcontacts is rows 1-10

i paste 10 rows from another workbook 3 are new, so it removes 7 duplicates

my range (in name manager) should be 1:13, but it shows 1:20 with 7 blanks at the bottom

'Dim currentworkbook As String
'currentworkbook = ActiveWorkbook.Name
'Workbooks.Open ("q:\mis\_estimatorm1.xlsm")
  'Workbooks("_estimatorm1.xlsm").Worksheets("contacts").Range("Allcontacts").Copy
'Workbooks(currentworkbook).Activate
'Sheets("contacts").Rows("3:3").Select
'Selection.Insert Shift:=xlDown
'Application.CutCopyMode = False

 '     Sheets("contacts").Activate
 '     Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder _
  '      :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

ActiveSheet.Range("allcontacts").RemoveDuplicates Columns:=Array(1, 3, 4), Header:=xlNo


'Columns("A:m").Select

   ' ActiveWorkbook.Worksheets("CONTACTS").Sort.SortFields.Clear
    'ActiveWorkbook.Worksheets("CONTACTS").Sort.SortFields.Add Key:=Range( _
     '   "A2:A2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
      '  xlSortNormal
    'With ActiveWorkbook.Worksheets("CONTACTS").Sort
     '   .SetRange Range("A1:m2000")
      '  .Header = xlYes
       ' .MatchCase = False
        '.Orientation = xlTopToBottom
        '.SortMethod = xlPinYin
        '.Apply
    'End With
Sheets("QUOTE").Activate
Range("A10").Select

I made everything notes except for the line of code that i know i causing the problem

score 4 · Accepted Answer · answered Jan 04 '16 at 00:27

Go back to Formulas ► Name Manager and redefine your allcontacts named range so that the Refers to: is dynamic. Something like,

'allcontacts Refers to:
=CONTACTS!$A$1:INDEX(CONTACTS!$M:$M, MATCH("zzz", CONTACTS!$A:$A))

That assumes text in column A and that column A typically always has a value when it should. If you had numbers in column A then it would be,

'allcontacts Refers to:
=CONTACTS!$A$1:INDEX(CONTACTS!$M:$M, MATCH(1e99, CONTACTS!$A:$A))

remove duplicates in named range

1 Answers1