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