VBA & Macro Problems

How to Create a Random 5% Sample of Filtered Excel Data with VBA

Muhammad Talha Oct 10, 2026 869 views

Question details

The user needs to generate a random 5% sample from a filtered dataset in Excel using VBA, avoiding runtime errors caused by resizing noncontiguous filtered ranges.

How to Extract a Random 5% Sample of Filtered Excel Data Using VBA

Product: Excel
Device & OS: not provided
Scenario: Extracting a random subset of data that meets specific filter criteria for analysis, auditing, or reporting purposes.
Observed behavior: Using the Resize method on SpecialCells(xlCellTypeVisible) fails because filtered data consists of multiple noncontiguous areas. Removing Resize copies all visible rows instead of isolating a 5% sample.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in the Developer tab before executing the VBA code.

Solution 1Recommended

Use Helper Columns with the RAND() Function in VBA

This approach avoids the noncontiguous range error by using helper columns to generate random numbers, sorting the data, and then extracting exactly 5% of the visible rows.

Because SpecialCells(xlCellTypeVisible) often returns noncontiguous areas, resizing it directly triggers an error. By generating a random number for each row and sorting, we can group a random subset together before copying.

Add an original order helper column

Use VBA to insert a new helper column next to your data and populate it with sequential row numbers. This ensures you can restore the initial data order later.

Add a random value helper column

Insert a second helper column and populate it with the =RAND() formula via your VBA script to assign a random decimal value to each row.

Apply your data filter

Use the AutoFilter method to filter your dataset based on your required criteria (e.g., filtering a status column for specific text).

Sort by the random values

Sort the visible, filtered data ascending or descending by the RAND() helper column to completely shuffle the filtered results.

Calculate and copy the 5% sample

Count the total number of visible matching rows, calculate 5% of that total, and copy that specific number of top visible rows to a new worksheet.

Restore order and clean up

Sort the original data by the first helper column to restore its original order, then delete both helper columns to leave the dataset intact.

Use Helper Columns with the RAND() Function in VBA

Script Efficiency: This logic is highly reliable for large datasets as it bypasses Excel's limitations with disjointed cell selections during copy-paste operations.

Solution 2

Use Excel's Top 10 Filter Feature with RAND()

A simpler alternative utilizing Excel's built-in 'Top 10' filter set to a percentage to extract a 5% random sample without complex VBA array counting.

Solution 3

Copy All Filtered Data First and Trim to Sample Size

Bypass the SpecialCells limitation by copying the entire filtered dataset to a new sheet first, then deleting the rows that exceed the 5% sample size.

Process Data and Run Macros with WPS Spreadsheet

WPS Spreadsheet offers powerful data processing capabilities, including full support for VBA macros and complex formulas like RAND(). You can easily filter, sort, and extract data subsets efficiently in a familiar environment.

1. Open your data file: Launch WPS Spreadsheet and open your existing data file.
2. Access the VBA Editor: Navigate to the Developer tab, open the VBA Editor, and paste your random sampling script.
3. Run the extraction macro: Execute the macro to automatically generate helper columns, filter the data, and extract the 5% sample to a new sheet.
4. Utilize built-in filters manually: Alternatively, use the Data tab to apply custom filters and sort by a random column manually without coding.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv).Supports advanced VBA and macro scripts for automated data extraction.Built-in advanced filtering and sorting tools to easily sample data.Lightweight software that loads large datasets quickly without crashing.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the SpecialCells(xlCellTypeVisible) method throw an error when resizing?

Filtered data often consists of multiple noncontiguous rows. The Resize property requires a continuous block of cells, so attempting to resize a fragmented range of visible cells results in a VBA runtime error.

Can I use the RANDBETWEEN function instead of RAND() for this task?

While RANDBETWEEN generates random integers, RAND() generates precise decimal values. Using RAND() minimizes the chance of duplicate random values, making the sorting and sampling process more accurate.

How do I ensure my random sample updates automatically?

Because RAND() is a volatile function, it recalculates every time the worksheet calculates. If you want a static sample, you should copy and paste the random values as values before filtering, or script your VBA macro to do so.

Will this VBA method work seamlessly across different operating systems?

Yes, standard VBA operations like adding columns, filtering, and copying work correctly as long as macros are enabled and there are no OS-specific file path or directory references in the underlying code.

Excel & Data Problems