Select specific number of rows on filtered range

indubala

New Member
Joined
Apr 8, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
I have a huge data set in which I would like to apply a filter and then select data based on a count within the filtered range (i.e. 500)

Lets say I have a list of products (Product names as A, B, C, D etc.) in column G.

1. I want to apply filter on column G.
2. Select the first product (A in this example). Selection should be dynamic as the product names may differ.
3. Check the count of the first product's visible data, If the the count of Product 'A' is less than 500, then
4. Select the next product also (A+B) and check the count. If the counts crosses 500 then only select A and copy the data
5. If A+B count is still less than 500 then select product C (A+B+C)

Basically I want to check and make a combination of data which is 500 or less. But do not want to make very small combinations also like 100 or 200 Hence need to check counts by combining products and try to make a combination of approximately 500 rows or less.

I need a VBA code to do this task for me. Please help.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Watch MrExcel Video

Forum statistics

Threads
1,130,157
Messages
5,640,465
Members
417,144
Latest member
Pitsi Mokoele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top