locked
RemoveDuplicates Run-Time error '1004' RRS feed

  • Question

  • I really need to set aside some time for official VBA training so I can do this on my own, until then I need your help with what I thought would be a simple method to try. All I want to do is remove rows based on the duplicates in column B of my active sheet. I thought something like the following would have worked, but none of these lines worked.

     

    ActiveSheet.Range("B").RemoveDuplicates Column:=("B"), Header:=xlYes
    
    ActiveSheet.Range(strExtract).RemoveDuplicates Column:=2, Header:=xlYes
    
    ActiveSheet.Range("Extract").RemoveDuplicates Column:=2, Header:=xlYes

     

    strExtract = "Extract"

    "Extract" is the name of the Table and the name of the Active Worksheet.

    Am I missing something with the RemoveDuplicates method?


    I'm new, please help. Begging for forgiveness in advance.
    Saturday, October 15, 2011 9:56 PM

Answers

  • So now I need to figure out the code that tells me the Table name on the active sheet. Then I can setup a variable to capture it and use that variable in my name change code...

    Tables have an index number on each worksheet starting from 1 just the same as worksheets have an index number starting from the left. Therefore the tables can be referenced by the index number in lieu of the table name.

    Do I interpret correctly that there is only one table on the worksheet because if so, it will be index 1.

    The following returns the name of the first table on the worksheet.

    strTblName = ActiveSheet.ListObjects(1).Name
    MsgBox strTblName   'for testing only

    If you have multiple tables on the worksheet and you delete say the first one then the index number of each table gets renumbered starting from 1. Top left table is 1 and the tables are numbered across then down.

    If you know the index number of the table then it can be used directly in lieu of the table name like the following.

    ActiveSheet.Range(ActiveSheet.ListObjects(1) _
          .DataBodyRange.Address).RemoveDuplicates Columns:=2, _
          Header:=xlNo

    As a little added extra, the following example returns names of all of the tables on the worksheet.

    Dim lstObj As ListObject
    Dim strTblName As String

    strTblName = ActiveSheet.ListObjects(1).Name
     
      For Each lstObj In ActiveSheet.ListObjects
        strTblName = lstObj.Name
        MsgBox strTblName   'for testing only
      Next lstObj


    Regards, OssieMac
    • Marked as answer by JMStumpf Monday, October 17, 2011 5:51 PM
    Sunday, October 16, 2011 7:53 PM

All replies

  • "Extract" is the name of the Table and the name of the Active Worksheet.

    Because it is the Table "Extract" from which you want to remove the duplicates then you need to reference the table. A table is referenced as a ListObject in VBA.

    Edited since first posted with simpler method.

    ActiveSheet.ListObjects("Extract").DataBodyRange _
          .RemoveDuplicates Columns:=2, Header:=xlNo

    Note that DataBodyRange is the data only excluding the table headers and hense Header:=xlNo 


    Regards, OssieMac
    • Edited by OssieMac Saturday, October 15, 2011 11:58 PM
    Saturday, October 15, 2011 11:44 PM
  • Bah, I thought it would be something like that I just didn't know the syntax, I had tried a couple of ListObject lines but they didn't seem to get any further.. Now I'm getting a Run-time Error '9' Subscript out of range when using ...

    ActiveSheet.ListObjects("Extract").DataBodyRange.RemoveDuplicates Columns:=2, Header:=xlNo 

    EDIT...

    Disregard, I forgot this is in a different part of my subscript.. The table I'm removing duplicates from isn't named and is on another worksheet than I was thinking.. let me try a different approach with the above code.

    Thanks OssieMac!

    EDIT of my EDIT...

    Ok, I think I will have to re-code this portion to name the table upon creation so that I can reference it in the "remove dupes" piece I'm trying to use to clean my data. I can only assume that something like ..

    DataTable.TableName = strAssociateName ...or some syntax will name the table when I create it on the worksheet.. time for more research ;)


    I'm new, please help. Begging for forgiveness in advance.




    • Edited by JMStumpf Sunday, October 16, 2011 2:44 AM
    Sunday, October 16, 2011 2:23 AM
  • The easiest way to get the syntax for naming a table upon creation is:

    Start the macro recorder.

    Select the range for the table and then insert the table.

    Stop the macro recorder.

    Edit the table name in the recorded code to the one that you want to use.


    Regards, OssieMac
    Sunday, October 16, 2011 5:03 AM
  • Not really sure how I'm going to accomplish the result I want but I'm almost there. I've been making it up as I go along so far. My project consists of 4 main worksheets, Stats, Extract, Lookup, Template. New employee sheets are created as the macro runs.

    The end results of all the macro work is presented on the team "Stats" sheet.

    I pull a massive extract from our ticketing database, each week / month and put this raw data in the "Extract" sheet.

    I have a sheet that contains various associate information, names, ID#'s, etc.. I use this for the "Lookup" sheet.

    The last sheet is "Template" and I use it as, well, a template, to create new sheets for each associate.

    Currently, the macro I've been building makes a copy of the 'Template' sheet and renames the copied worksheet to an associate name. I'd like to find a decent line of code that I can use that will then rename the table that was copied on this new associate sheet, based of another variable I have for strAssociateID.

    This way I will have individual worksheets and tables that specifically reference a single employee's information. 

    I will play around with the macro recorder and see if it can tell me the proper way to name a table with VBA code. 

    As always, many thanks for your suggestions OssieMac!

    Jason


    I'm new, please help. Begging for forgiveness in advance.
    Sunday, October 16, 2011 1:19 PM
  • OK, so far I've reworked the code to utilize employee ID's and naming the tables would be easy if Excel had a predictable way of naming tables that are copied to other sheets.

    I renamed the Table on my Template sheet to "TempTable" .. thinking that Excel would name a copy as TempTable1.. but no.. it's TempTable120 ?!!?

    So now I need to figure out the code that tells me the Table name on the active sheet. Then I can setup a variable to capture it and use that variable in my name change code...

    Any off for more research to find out how to get vba to return the table name..


    I'm new, please help. Begging for forgiveness in advance.
    Sunday, October 16, 2011 7:16 PM
  • So now I need to figure out the code that tells me the Table name on the active sheet. Then I can setup a variable to capture it and use that variable in my name change code...

    Tables have an index number on each worksheet starting from 1 just the same as worksheets have an index number starting from the left. Therefore the tables can be referenced by the index number in lieu of the table name.

    Do I interpret correctly that there is only one table on the worksheet because if so, it will be index 1.

    The following returns the name of the first table on the worksheet.

    strTblName = ActiveSheet.ListObjects(1).Name
    MsgBox strTblName   'for testing only

    If you have multiple tables on the worksheet and you delete say the first one then the index number of each table gets renumbered starting from 1. Top left table is 1 and the tables are numbered across then down.

    If you know the index number of the table then it can be used directly in lieu of the table name like the following.

    ActiveSheet.Range(ActiveSheet.ListObjects(1) _
          .DataBodyRange.Address).RemoveDuplicates Columns:=2, _
          Header:=xlNo

    As a little added extra, the following example returns names of all of the tables on the worksheet.

    Dim lstObj As ListObject
    Dim strTblName As String

    strTblName = ActiveSheet.ListObjects(1).Name
     
      For Each lstObj In ActiveSheet.ListObjects
        strTblName = lstObj.Name
        MsgBox strTblName   'for testing only
      Next lstObj


    Regards, OssieMac
    • Marked as answer by JMStumpf Monday, October 17, 2011 5:51 PM
    Sunday, October 16, 2011 7:53 PM
  • Man, that makes so much more sense now.. If you are ever in Richmond, VA.. The first round is on me ;) .. Let me try working this into my code. be back in a few

     Yea that totally works so much better, Thanks!


    I'm new, please help. Begging for forgiveness in advance.
    • Edited by JMStumpf Monday, October 17, 2011 5:51 PM
    Sunday, October 16, 2011 8:13 PM