+ Reply to Thread
Results 1 to 5 of 5

Can Range(Cells(1, 1), Cells(16, 1)) not be used with the Advanced Filter?

  1. #1
    Registered User
    Join Date
    12-04-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    38

    Question Can Range(Cells(1, 1), Cells(16, 1)) not be used with the Advanced Filter?

    I have a workbook and the only data in it is on Sheet1. In A1 I have the word "status". In A2 through A16 I have random text (such as banana, apple, and orange) and some of the values are duplicated/repeated in the column (in A2 through A16). I have created the below VBA code to take the unique values from this list and copy them to a new sheet. It works fine.

    Please Login or Register  to view this content.
    However, I'd like to replace the bottom most line of code with this, using Range(Cells(1, 1)), Cells(16, 1) instead of Range("A1:A16").

    Please Login or Register  to view this content.
    But it does not work and gives me an error message stating: Run-time error '1004': Method 'Range' of object '_Global' failed. Does anyone know why? Thanks.
    Last edited by Ragnaros; 12-12-2012 at 10:39 AM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Can Range(Cells(1, 1), Cells(16, 1)) not be used with the Advanced Filter?

    you need to qualify the cells properties with the worksheet and you were missing parentheses for the Range properties
    Please Login or Register  to view this content.
    note all the periods
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    12-04-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Can Range(Cells(1, 1), Cells(16, 1)) not be used with the Advanced Filter?

    Thanks. The missing parentheses was a typo that I had made while creating the topic. I altered the code as you advised but now it is deleting all values in A2 through A16 in Sheet1 and is not copying them over to the new sheet. My code looks like this now.

    Please Login or Register  to view this content.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Can Range(Cells(1, 1), Cells(16, 1)) not be used with the Advanced Filter?

    oh yeah-I got carried away ;-)
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-04-2012
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: Can Range(Cells(1, 1), Cells(16, 1)) not be used with the Advanced Filter?

    Okay, ultimately this is what I needed. Thanks for your help!!!

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1