hi Saarang,

Thank you for identifying & providing the link to the crosspost -
hopefully we can help.




Saarang84;740535 Wrote:

...


VBA Code:
--------------------


Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i
--------------------



I want to mark only certain number of rows (stored in the variable
randRow) as samples. But the above line of code marks all the rows
filtered. How can this be fixed??



I don't think it's very likely but does the below work?


VBA Code:
--------------------


Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible).resize(randR ow,1).value2 = "Sample_" & i
--------------------



If not, then you may have to loop through each area within the range of
visible cells until you get to the number of rows represented by
randRow. I'll look through your code in detail over the weekend & see if
I can come up with an answer for you...

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

Hi Rob,

I've checked out your code and it doesn't fix this requirement.

Out of the total number of rows filtered, say y, i need to choose x
rows *randomly* (where x<y). This x rows to be marked are stored in
-randRow-. The below line of code marks all the rows filtered. But i
need only x rows to be marked.


Sarang


--
Saarang84
------------------------------------------------------------------------
Saarang84's Profile: http://www.thecodecage.com/forumz/member.php?u=2386
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

Saarang84 expressed precisely :
Hi Rob,

I've checked out your code and it doesn't fix this requirement.

Out of the total number of rows filtered, say y, i need to choose x
rows *randomly* (where x<y). This x rows to be marked are stored in
-randRow-. The below line of code marks all the rows filtered. But i
need only x rows to be marked.


Sarang

How about using a minimum row and a maximum row to construct your list
of filtered rows, then randomly choosing rows from that list? It would
require building a delimited string of the filtered row numbers, and
testing the string for your random row numbers using the InStr()
function. The random numbers would be created by a function that takes
a Min and a Max number, and then returns a random number between those.

Would this work for you?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

hi Sarang,


Saarang84 Wrote:

Hi Rob,
Thanks for the reply. Your piece of code doesn't work.
'Filter Randomly based on Inputs' (http://tinyurl.com/2uqrugj)
Hope this thread (coded by mikerickson) helps...
Sarang


Please keep the responses in the thread so that any other helpers or
people looking for answers can also see the same information.



Out of the total number of rows filtered, say y, i need to choose x
rows randomly (where x<y). This x rows to be marked are stored in
randRow. The below line of code marks all the rows filtered. But i need
only x rows to be marked.


Do you want x rows marked from each time the filter changes on the
below lines of code?
Or do you want a total of x rows (after all the looping is done)?



VBA Code:
--------------------


For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

broro183;740587 Wrote:

hi Sarang,


Please keep the responses in the thread so that any other helpers or
people looking for answers can also see the same information.


Do you want x rows marked from each time the filter changes on the
below lines of code?
Or do you want a total of x rows (after all the looping is done)?



VBA Code:
--------------------


For i = 1 To 10
Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




Rob



Hi Rob,

Based on the number of rows filtered (by the below line) the value of x
is calculated. Out of the total no. of rows filtered by this line, these
x rows are to be marked as samples *randomly*.



VBA Code:
--------------------


For i = 1 To 10
Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




For example, when i=1, say the number of rows filtered may be 200, then
x=10 rows (5% of 200), need to be marked as samples. When i=2, say the
number of rows filtered is 150, then If x=7.5 rows (5% of 150) or 8 rows
(ceiling value) need to be marked as samples randomly. This 5% of total
visible rows is just an example, actually its z% of total visible rows
in the filter from which x rows are calculated and to be chosen
randomly.

Hope this clarifies better.

Since this is a test data, i use a for loop (from 1 to 10) and filter
each time for 1,2 ...10. Actually, my original data contains a list of
names in an array (called names(i), and i=1 to n, for n names in
general)


Sarang


--
Saarang84
------------------------------------------------------------------------
Saarang84's Profile: http://www.thecodecage.com/forumz/member.php?u=2386
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

With a random row chooser of course!


On Sat, 4 Sep 2010 19:15:32 +0100, broro183
wrote:


hi Sarang,


Saarang84 Wrote:

Hi Rob,
Thanks for the reply. Your piece of code doesn't work.
'Filter Randomly based on Inputs' (http://tinyurl.com/2uqrugj)
Hope this thread (coded by mikerickson) helps...
Sarang


Please keep the responses in the thread so that any other helpers or
people looking for answers can also see the same information.



Out of the total number of rows filtered, say y, i need to choose x
rows randomly (where x<y). This x rows to be marked are stored in
randRow. The below line of code marks all the rows filtered. But i need
only x rows to be marked.


Do you want x rows marked from each time the filter changes on the
below lines of code?
Or do you want a total of x rows (after all the looping is done)?



VBA Code:
--------------------


For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




Rob

On 9/5/2010 12:35 AM, Spurious Response wrote:
With a random row chooser of course!

I have to admit the same thing occurred to me, e.g.

http://www.techonthenet.com/excel/formulas/rnd.php

MS Excel: Rnd Function (VBA only)

In Excel, the Rnd function allows you to generate a random number
(integer value). You can specify the random number to be a value between
2 user-specified numbers.

The syntax for the Rnd function is:

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

upperbound is the highest value that the random number can be.
lowerbound is the lowest value that the random number can be.

Applies To: Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

Int ((6 - 1 + 1) * Rnd + 1) 'return random # between 1 and 6.
Int ((200 - 150 + 1) * Rnd + 150) 'return random # between 150 and 200
Int ((999 - 100 + 1) * Rnd + 100) 'return random # between 100 and 999

VBA Code

The Rnd function can only be used in VBA code. For example:

Dim LRandomNumber As Integer
LRandomNumber = Int ((300 - 200 + 1) * Rnd + 200)

In this example, the variable called LRandomNumber would now contain a
random number between 200 and 300.

broro183;740699 Wrote:

hi Sarang,

I haven't spent much time on this & I'm sure that others could provide
a much neater solution - for your sake, I have my fingers crossed that
someone does! emb1

I took the easy option (instead of understanding the existing code!) &
did some googling which led me to the below page on Tushar's site & have
incorporated that into your file as a multi step process (see my Notes
on the Summary sheet).


'Random Selection'
(http://www.tushar-mehta.com/excel/ne...and_selection/)

Note: I imagine this could be turned into a single step process with a
bit more thought/knowledge...

btw, if you do (or already have) come up with a solution, can you
please post it for me & others to learn from?


Rob




Hi Rob,

Sorry for avoiding links to the cross posts i'd made across forums.
I tried executing the part of code (using breakpoints) where you had
made additions. However, I'm not able to execute the code in my
RandomFilter module, particularly from the below line, where i get an
runtime error #424(Object required):



VBA Code:
--------------------


For Each cll In rngArea
Set FinalSampleRng = Union(FinalSampleRng, cll) ' This line doesn't work. Pops a runtime error #424 (Object required)
Next cll
--------------------




I'm not able to troubleshoot this, and hence unable to understand the
execution / workflow of the code due to the above error.

Can you explain (put it in simple words) as to what you are trying to
accomplish with your code (which you've done)??


Sarang


--
Saarang84
------------------------------------------------------------------------
Saarang84's Profile: http://www.thecodecage.com/forumz/member.php?u=2386
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

Saarang84;740740 Wrote:

Hi Rob,

Sorry for avoiding links to the cross posts i'd made across forums.
I tried executing the part of code (using breakpoints) where you had
made additions. However, ....Sarang

Saarang84, until you post the links to ALL your crossposts both here
and at the other forums you have posted in i can not allow Rob to spend
any more of his valuabletime helping you, i have already explained to
Rob that he should be charging you for this amount of work as it is
beyond the realms of forum help.

If you wish to avoid posting cross post links ere and at every other
forum then you must go here
http://www.thecodecage.com/forumz/aw...payment&sid=ss and
purchase at a minimum VIP Gold 3 months, you will then have access to
the solution centre where Rob can quote you for the extra work and you
can choose to accept his offer.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

Hi Rob,

Listed below is my way of approach to handle the problem (the old
school array logic which i proposed initially):

Steps:

1. Since i need x rows marked randomly from each time the filter
changes on the below lines of code,



VBA Code:
--------------------


For i = 1 To 10
Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




I intended to use an array and to read and input the row numbers into
it (instead of using random values generated by RAND() function)

In other words, instead of the below line:



VBA Code:
--------------------


Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=RAND()"
--------------------




I thought of changing it as below to populate row numbers at the end of
each row of data:



VBA Code:
--------------------


Range(.Cells(2, fCol), .Cells(LR, fCol)).FormulaR1C1 = "=ROW()-1"
--------------------




This would generate and populate the row number against each row
[instead of RAND()] under the column titled Flag. Then value-paste this
column.

2. Now, search for this Flag column and read these row numbers for all
visible rows (in filter) during each iteration of the for loop after
applying filter and store these row numbers in the array.

3. I know little bit terms with array coding and Redim may need to be
used inbetween somewhere. Randomize and choose the row numbers from the
array and choose the required number of rows to be marked as samples
(the required number of rows to be marked as samples is calculated and
stored in *randRow*). Tushar's code may need to be used here.

4. Mark the randomly chosen rows as samples using the below line code
(needs to be tweaked a bit):



VBA Code:
--------------------


Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i
--------------------




5. After marking the sample rows chosen, flush the contents of the
array before starting the next iteration where the filter is changed.

6. Proceed with next iteration. Process steps 1 to 5 again.

The only thing i'm concerned about implementing the above steps is to
use memory efficiently since arrays are used. I've limited knowledge for
coding with arrays, can anybody who can play around coding with arrays
help me out??


Sarang


--
Saarang84
------------------------------------------------------------------------
Saarang84's Profile: http://www.thecodecage.com/forumz/member.php?u=2386
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

Saarang84;740742 Wrote:

Hi,

Cross-post links for this thread is as below:

'How to choose Random rows??'
(http://www.mrexcel.com/forum/showthread.php?p=2442594) - MrExcel
Message Board
'Random Selection of Row Numbers from a Filtered Range'
(http://tinyurl.com/243vb6l) - Excel Forum
'How to select Random Rows after Autofilter??'
(http://tinyurl.com/22nw2f2) - XtremeVBTalk Forum
'How to select Random Rows after Autofilter??'
(http://www.vbaexpress.com/forum/showthread.php?t=33907) - VBA Express
Forum

I REALLY DO NOT mean to abuse any of the forums where help is on offer,
as well as not solely for the purpose of uploading attachments. My only
intention was to make it viewed by more people so that i could get help
instantly. My sincere apologies if this is understood in any other way.


Sarang

Sarang, what you need to realise is that crossposting doesn't get you a
wider audience, in actual fact it shrinks your audience as you begin to
alienate people. All the people that visit here to help also visit all
the other forums to help, seeing their efforts crossposted annoys them
to the fact that they stop helping, so if you must crosspost ALWAYS
provide links :)


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---