+ Reply to Thread
Results 1 to 8 of 8

Defining range of visible rows after autofilter.

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Defining range of visible rows after autofilter.

    Hello all,
    I have been using this forum with great effect but I have come up against a problem that has beat me for the last few days and I'm hoping you can help!!!

    I want to perform a calculation on only the filtered rows of a sheet after using an autofilter. I had planned on using the SpecialCells function to do this but my problem is that I have trouble specifying the range for the caluculations as the number of rows will change depending on the original dataset and/or the criterion I use to filter for. If my sepcified range is too big excel returns #VALUE! for the rows below the bottom of my filtered data. I plan on using the subtotal function to find the average of the results of the calculation in my next step but the #VALUE! is proving problematic. Below is the code I use to filter and after that is the code where I don't know how to correctly specify my range. Please help!!

    Please Login or Register  to view this content.
    Now the troublesome one!


    Please Login or Register  to view this content.
    Thanks in advance for your help.

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

    Re: Defining range of visible rows after autofilter.

    May be

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Defining range of visible rows after autofilter.

    Thanks so much for your help, but unfortunately that just places the following forumula in Cell I1 =TIMEVALUE(RIGHT(G1,8)).
    Just to be clear, I need to insert that forumula in every row of Columns I and J except the header, after a filter has been applied to Column F. Columns I and J are originally blank.

    Thanks again for your help. I'm wondering if I am using the SpecialCells function correctly. Any suggestions?

  4. #4
    Registered User
    Join Date
    04-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Defining range of visible rows after autofilter.

    UPDATE:

    I just got a hint from another question just posted and changed my Macro to the following:

    Please Login or Register  to view this content.
    This solved my problem but can anyone provide a link to help me figure out how definining Dim lngLastRow As Long affects any further macros i plan on using. E.G. I will be using the following macro to input a calculation into the first visible row in column L

    Please Login or Register  to view this content.
    As you can see I'm defining a range for that step, will that affect the definition in the previous step?
    FINALLY, is there a more efficient way of doing the above Average calculation?

    Thanks again for all the help!!

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

    Re: Defining range of visible rows after autofilter.

    You have not got a variable called lngLastRow on your code
    Please Login or Register  to view this content.
    You can define any variable, but may not use it on your code.

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Defining range of visible rows after autofilter.

    I'm sorry, perhaps I'm not using the correct terminology.
    In my second Macro, I define the variable Dim lngLastRow As Long and use it to perform the calculations in columns I, J and K . In the third macro I define the variable Dim rngFilt As Range and use this to find the first visible cell in row L. From my understanding, once I define this range, I can't define another range, or am I wrong?
    Thanks again for your help.
    Regards,

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Defining range of visible rows after autofilter.

    No, you are! It was my mistake, I did not see that bit of your code.


    Please Login or Register  to view this content.
    Would be fine as long as column "F" has the longest range in your data, i.e. the lowest one, so that your code goes all the way to the bottom, but you can use this one and no need to worry which column.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Defining range of visible rows after autofilter.

    Thank you very much for your help. That worked a treat. As an aside, it probably isn't necessary as I am using a filtered data so the colum which I am filtering will always be the longest.
    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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