Random Selection of Row Numbers from a Filtered Range

Hi,

I'm trying to get only the row numbers from a filtered range. These row numbers should be stored in an array which will be used later in the code and should exclude the header row.

Below is the code that i tried out. There are 2 different types of msgbox statements which i tried (refer msgbox statements just before the end Sub statement) alternatively (by commenting one of them at a time).

rngVisible.Address gives me the entire filtered range
rngVisible.Row gives me the only one individual row number (1st row number in the filtered range)

Please Login or Register  to view this content.

How to get only the visible row numbers and store them in an array??
Sample workbook attached.

Sarang

Hi Wizards,

I managed to write the code below (at the end of the post) to store the visible row numbers in an array. However, before proceeding with every iteration of the outer for loop, i want to randomly choose k rows from the array before flushing it. This number k is calculated as z% of visible rows output by the filter.

For example, if there are 1000 rows in a sheet where filter is applied to get 250 rows, then k = 12.5 (or 13 - always need to take the ceiling value) when z=5.
Now, i want to choose 13 random elements (row numbers) from the array. How can that be done? After randomly choosing the elements, i execute the below piece of code before flushing the array contents and changing the filter (Next x - Outer for loop)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Sarang

You need to process each area in the visible range. Here is some code to count the number of rows. Insert it in your last else block. Once you get the count you can redim an array and load whatever you want into it.

Please Login or Register  to view this content.

Why don't you use advancedfilter with xlfiltercopy ?

Yes by using advancedfilter: all 'visible rows' will be displayed in an unbroken range.

Hi,

I found the below link by Tushar Mehta on Random Selection

Here's my code where i'm trying to incorporate some code from the above link.

Please Login or Register  to view this content.

Using .FormulaR1C1 = "=ROW()-1" shows row numbers in a column.
After applying filter to DATA1 column in the sheet, i want to read the visible row numbers and store it in an array.

I'm trying to include the RandomSelect method below (from the above link) to select random row numbers by mixing the (above mentioned) array and mark the selected array elements. The randRow variable contains the number of rows to be chosen from the array.

Return a specified number of random values from an user specified array

Please Login or Register  to view this content.

I'm not able to incorporate and invoke the above module. Can someone help me incorporating it??

Sarang

hi everyone,

This is question is cross posted at both the below links (& maybe others...):
http://www.thecodecage.com/forumz/ex...ndom-rows.html
http://www.mrexcel.com/forum/showthread.php?t=491418


Sarang,
As Zack has mentioned at MrExcel earlier today - please don't abuse other forums for the purpose of uploading attachments (or by creating unlinked crossposts)...
Can you please go through each Forum that you have posted this question in, and provide links to ALL other Forums where you have asked this question?
This will inform helpers at each site. Informed helpers can then see what others have suggested (at the different sites), and more importantly, to decide if they want to continue helping when you may be following a different approach.

For example, have you tried the code in the file that I uploaded in http://www.thecodecage.com/forumz/740699-post7.html?
If so, can you please post any comments in that thread?


Rob

read up on how to cross-post politely in the link in my signature.

And, in general, take another close look at our forum rules. You have managed to break more than one recently, so a refresher may be called for.