+ Reply to Thread
Results 1 to 21 of 21

Autofilter Method of Range class failed

  1. #1
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Autofilter Method of Range class failed

    Keep on getting -
    "Runtikme error 1004:
    Autofilter method of range class failed"

    After running the macro again from its initial run. Is there a loop or if statement I can use to keep the macro from creating an error and ultimately delete everything?
    Its the "Status" Macro.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Autofilter Method of Range class failed

    The filter row syntax is wrong.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    Still gives error after correction. Any other ideas?

  4. #4
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Autofilter Method of Range class failed

    Why you did not say anything? You will get another error as all rows will be hidden. Every single row will match the criteria and hence all rows will be hidden. There is nothing left to delete.

  6. #6
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    My apologies, I am fairly new to VBA. Eventually the code just eats everything and I would like to tell it to stop as soon as there is no more criteria to go after.
    If rngKeep = "" Then
    Skip rngDelete

  7. #7
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Autofilter Method of Range class failed

    If you have issues with the code I wrote then why not ask me for help in the original thread instead of starting another thread?

    Please Login or Register  to view this content.
    Last edited by skywriter; 10-29-2015 at 01:01 PM.

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Autofilter Method of Range class failed

    This line is the culprit.
    rngKeep.Rows.Hidden = True
    As I said, all rows are eligible to be hidden. I also think the hidden property does not hide visible cells only.

  9. #9
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Autofilter Method of Range class failed

    Every item in his workbook matched the criteria, therefore nothing got filtered so I put in a line that checks if the filtered range is the same size as the original current region, if so it exits the sub.
    See post #7.

    Quote Originally Posted by AB33 View Post
    This line is the culprit.
    rngKeep.Rows.Hidden = True
    As I said, all rows are eligible to be hidden. I also think the hidden property does not hide visible cells only.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Autofilter Method of Range class failed

    This line is the culprit.
    rngKeep.Rows.Hidden = True
    As I said, all rows are eligible to be hidden. I also think the hidden property does not hide visible cells only.

  11. #11
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    Thanks again skywriter! My apologies for not going into the previous post. Figured once its "SOLVED", its a done post.
    I tried doing the method you posted before asking for help again, except I didnt name a variable such as "c" to count the rows.
    If rngKeep = "" Then Exit Sub
    But that obviously didnt work.
    May I bother you for more help on averaging each column separately?
    Im currently attempting the code.

  12. #12
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Autofilter Method of Range class failed

    So I use the with statement so we are filtering the current region.
    RngKeep is set by the visible cells that are left after autofiltering. In testing after removing the autofilter I have selected that range and it seemed to be working fine. I then hide that range and use special cells again to set another range based on the visible cells left in the current region. I then delete this last range and make the rngKeep cells visible.
    The OP ran this code again after the criteria he originally wanted deleted was deleted.
    I made a change to the code that checks if the size of rngKeep is the same as the original current region, which would be indicative of whether there are any visible rows left after autofiltering.

    Am I missing something?

  13. #13
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    I dont think you are missing anything skywriter.
    rngKeep kept all the desired criteria hidden, then rngdelete is designated to delete everything outside of the criteria row by row. right?
    I assume when you named the variable "c", its purpose is to count the number of rows in the current region being used. Then it is compared to the amount of rows rngKeep has counted that are also being used?

  14. #14
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Autofilter Method of Range class failed

    rmrjr22 if you have other data that this isn't working with, then let me know. I'll modify the code or write new code. If you have different scenarios, let me know, I'll work with you. The code I wrote you is partially based on experience and partially based on experimentation.
    I am here to learn and when people present a scenario they would like to see it just helps me learn trying to make it happen, which is what I am here to do. I come here to learn by doing and by seeing how other people do it. I am completely self taught and do this strictly for fun.

  15. #15
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    Thank you Skywriter. I do appreciate the help and I too am trying to learn. Thank you for your patience. Im not 100% sure what each line of code does, but I am trying to understand. I asked before if my assumptions were correct, so I know what to use each command for. (Post #13)

  16. #16
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Autofilter Method of Range class failed

    rngKeep kept all the desired criteria hidden, then rngdelete is designated to delete everything outside of the criteria row by row. right?
    I assume when you named the variable "c", its purpose is to count the number of rows in the current region being used. Then it is compared to the amount of rows rngKeep has counted that are also being used?
    We filter the current region and the cells that are still visible are used to create a range called rngKeep.

    We then remove the filter so that everything is visible.

    We then only hide the rngKeep cells and delete whatever is still visible in the current region.

    When you have no data such as "fail" in your sheet then when you auto filter nothing happens, all the original data in current region is still there, so rngKeep is the same as the current region. Then when you hide rngKeep you in reality hide the whole current region, there is nothing left to delete.

    We compare the row count to c and if they are the same we exit, to avoid all of that.

    Maybe there's a better way to do it, but I don't know what it is. There may be other errors that come about. I usually learn more when things don't work right. I know there are probably flaws in what I wrote for you, but fixing issues is how I learn to write better code.

    If you want changes let me know.
    Last edited by skywriter; 10-29-2015 at 02:44 PM.

  17. #17
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    Wow, I now see the code clearer! All thanks to you. It feels good when you begin to understand something you never fully grasped before.
    Currently, I am trying to find the average of certain columns.
    I found a doozy of a code line that does just that, but only works for where the activecell is.
    Im thinking to direct the wrath of this code line to an array of column names.

    Dim cList
    cList = Array("Offset", "Zero", "Linearity", "Span", "Gain1", "Gain2", "TCS1", "TCS2", "TCZ1", "TCZ2")

    I would replace ActiveCell?

    Please Login or Register  to view this content.
    Last edited by rmrjr22; 10-29-2015 at 02:57 PM.

  18. #18
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Autofilter Method of Range class failed

    Don't confuse the way autofilter used that array to how you would otherwise use that array.
    Normally to refer to an item in an array you have to specify the index number of the item in the array to use that item.

    Unless you have Option Base 1 at the top of your code module the array you create using Array("item","item2" will be a zero based array, meaning the first item has an index number of 0 and the second item will be 1 etc.
    With Option Base 1 at the top of your code that array will use 1 for the first item 2 for the second etc.
    Regardless of whether you have Option Base 1 or not, if you use VBA.Array("Item1","Item2" etc., for your array it will always be a 0 based array.

    Play with the code below to understand what I am talking about.

    With the first code the way it is you will get an error because there is no 0 indexed item in that array. Remove or comment out the Option Base 1 statement and see what the message box gives you. Try experimenting, change item number and see what happens, remove or comment out the Option Base 1 statement and you will see how it changes things.

    LBound gets the number of the first item in an array regardless of the Option Base statement. It's immune to that, which is why people use it in code, it works regardless of whether Option Base 1 is in place or not. UBound gives the number of the last item in an array.


    Please Login or Register  to view this content.
    Last edited by skywriter; 10-29-2015 at 04:45 PM.

  19. #19
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    Thanks skywriter! I tried the code above. Very interesting. I changed all the parameters you specified and even tried a few other things to find which part depends on the other. I noticed when changing the option base to 0, everything became base 0, regardless of either array method. Perhaps I can incorporate this method into my averaging of columns per part number.

  20. #20
    Registered User
    Join Date
    10-27-2015
    Location
    CT, USA
    MS-Off Ver
    2010
    Posts
    36

    Re: Autofilter Method of Range class failed

    Think im going to open a new thread and submit this one as "SOLVED". Thank you everyone for time and patience.
    Last edited by rmrjr22; 10-30-2015 at 10:16 AM.

  21. #21
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Autofilter Method of Range class failed

    Quote Originally Posted by rmrjr22 View Post
    Thanks skywriter! I tried the code above. Very interesting. I changed all the parameters you specified and even tried a few other things to find which part depends on the other. I noticed when changing the option base to 0, everything became base 0, regardless of either array method. Perhaps I can incorporate this method into my averaging of columns per part number.
    Didn't I explain that?

    Unless you have Option Base 1 at the top of your code module the array you create using Array("item","item2" will be a zero based array
    Regardless of whether you have Option Base 1 or not, if you use VBA.Array("Item1","Item2" etc., for your array it will always be a 0 based array.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Autofilter method of Range Class Failed
    By slamdunka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2015, 03:24 AM
  2. AutoFilter Method Of Range Class Failed
    By goss in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-10-2015, 09:40 PM
  3. Autofilter Method of Range Class Failed
    By flindy87 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-02-2013, 11:51 AM
  4. AutoFilter method of Range class failed
    By AdLoki in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2011, 05:30 AM
  5. AutoFilter method of Range class failed - Yet autofilter works.
    By Carlsbergen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2009, 05:43 PM
  6. AutoFilter method of Range class failed
    By blopreste3180 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2006, 06:45 PM
  7. Autofilter method of range class failed
    By Terry K in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-17-2005, 11:05 PM

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