Tricky VBA to extract few rows with condition and few rows randomly

nagasree

New Member
Joined
Oct 30, 2021
Messages
30
Office Version
  1. 2019
Platform
  1. Windows
Hi Geniuses , I have a file with one sheet named as Audit file. It has multiple columns until column Z
The first column(A) has auditors name column (C) has regions and column (X) has decisions(Valid, invalid, correct incorrect and dropdown lists)
First row has headers.

Each person works on multiple regions,
I need to extract 27 rows for each person's each region and paste it in a new sheet and name it Verification (example: person A works on UK, US, IND,etc, similarly person B works on UK, SD, IS, etc, The code should extract for person A region UK 27 rows, For person A US region 27 rows, Person A IND region 27 rows, then move to the next person B UK region @7 rows and so on....)

In 27 rows for each persons each region, all the rows containing Valid in Column X must be included and the remaining rows can be randomly selected excluding the Valid values in column X.


How I do Manually is, first filter person A, then filter Region UK then Filter Valid decision, copy everything and paste in sheet Verification, the remove the filter and select the remaining rows randomly and paste them in Verifivation sheet, then I do it for next region, once Person A's regions are completed I move to Person B, and so on.

I know this is a tricky VBA but Im sure there will be many geniuses to help me out, Thank you in advance.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

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
Back
Top