# Defining range of visible rows after autofilter.

1. ## 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!!

Now the troublesome one!

May be

3. ## 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. ## 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:

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

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. ## Re: Defining range of visible rows after autofilter.

You have not got a variable called lngLastRow on your code
You can define any variable, but may not use it on your code.

6. ## 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?
Regards,

7. ## Re: Defining range of visible rows after autofilter.

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

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.

8. ## 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.

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

#### 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