Copy a fixed range of cells from a filtered Table

Hi Guys,

I Need a macro that filters my table my a certain criteria in column B, Sorts the table by descending magnitude of the field H, and then copies the top10 values of the field H. I have accomplished the first two tasks already. However, I cant select range H10:H19, since as the filters from the first column change, the first ten values could actually be in rows 25,27,34,56....etc How Can I select the range of the ten first VISIBLE rows as they appeaar after the filter? Thanks

Basically a piece of code that allows me to copy the first ten Cells below the "Nominal" Header Cell, regardless of whether they are rows 10-19 or 12,24,35,46,57,68,79...etc

Hi..

Using a UDF.. you can ascertain the First Visible Row after filtering...

Replace the variables like "YourSheet" and "YourCriteria" etc with your own...

Please Login or Register  to view this content.

Use it like..

Please Login or Register  to view this content.

Hi..

Upload a sample Workbook and i will take a look...

here you go, Thanks!

TESTAA.xlsmhere you go, Thanks!

Any Luck? :S

Hi..

I got into a bit of a mess.. I think i would have been better starting form scratch instead of trying to modify your code..

Having said that.. the part you needed ( copy top 10 rows after autofiltering table) seems to work..

Step through the code to see how it does it..

OK Ill get back to you! Fingers crossed... Thanks!

It works fine for the small spreadsheet but when I transfer the code to my big one it gives me an error in the function FirstVisibleValue


Line:Set R= Activesheet....

Error 9 - Subscript out of range.


Any ideas?

Yeah, I have a Sheets("Mysheet").Select just before the function call

In this case Sheets("F.C.SSA-View 2").Select

I have edited tyhe code above

Hi..

Can you paste the entire Function code as it appears in your Workbook...

Please Login or Register  to view this content.

Hi..

I don't know why its not working.. like you say.. it works as it should in the Test Workbook i sent..

All i can suggest is that you upload the workbook your working on and hopefully I or someone else can find the issue..

Apparently its too big to upload :S Whats the limit?