Solved Excel Macro Random Rows Results

August 21, 2015 at 11:40:17
Specs: Windows XP
In reference to a question I had asked previously.

If I have a sheet that contains less than 10 rows and want to get 10% of that, how can I ensure that my results will return at least one record at a minimum. I ask because right now, I have some sheets that only contain 4 rows and when I query 10% of it, I don't get any records because I assume it takes 4 * .01 and comes up with .4 and rounds down to 0 records being pulled.

message edited by JasonB

See More: Excel Macro Random Rows Results

Report •

August 23, 2015 at 05:08:01
✔ Best Answer
Here are 2 options:

This option will ensure that any number of rows less than 10 returns 1 row:

'Get 10% of that number, ensure at least 1 row
    percRows = numRows * 0.1
      If percRows < 1 Then percRows = 1

This option will Round Up all values to the next highest integer. e.g. 4 rows will return 1, 44 rows will return 5:

'Get 10% of that number, Round Up result to next integer
    percRows = Application.WorksheetFunction.RoundUp(numRows * 0.1, 0)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question