How to Randomly Pick Names from Multiple Columns in Excel Using VBA

Learn how to efficiently select random names from different columns in Excel with VBA code, enabling you to automate name picking tasks easily.
---
This video is based on the question stackoverflow.com/q/72444323/ asked by the user 'youbitto' ( stackoverflow.com/u/19238305/ ) and on the answer stackoverflow.com/a/72444979/ provided by the user 'FaneDuru' ( stackoverflow.com/u/2233308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Pick random names from different lists excel VBA

Also, Content (except music) licensed under CC BY-SA meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Randomly Pick Names from Multiple Columns in Excel Using VBA

Are you tired of manually picking random names from different lists in Excel? If so, you’re not alone. Many Excel users have similar requirements and need an efficient solution. This guide will guide you through a simple yet effective way to accomplish this using VBA (Visual Basic for Applications).

Understanding the Problem

Imagine you have a list of names located in a sheet called "Inscrp". You want to randomly select names from several columns of this sheet and display the results in another sheet called "Tirage". The primary challenges are:

Extracting random names from multiple columns.

Ensuring that no name is picked more than once.

The Structure

Sheet "Inscrp": Contains your name lists across several columns.

Sheet "Tirage": Where the randomly selected names will be displayed.

The goal is to select a specified number of names (HowMany), for each column in the "Inscrp" sheet, and display them on the "Tirage" sheet starting from a specified cell (CellsOut).

The Solution

Here’s a step-by-step approach to create a VBA macro that accomplishes this.

1. Opening the VBA Editor

To start, open Excel and press ALT + F11 to open the VBA editor. In the editor, you can insert a new module to paste the VBA code.

2. Writing the VBA Code

Use the following code to randomly select names from multiple columns of the "Inscrp" sheet:

[[See Video to Reveal this Text or Code Snippet]]

3. Explanation of the Code

Variables: The code defines several variables to manage the worksheets, track the number of names (HowMany), and manage the output cell (CellsOut).

Looping Through Columns: The code includes a loop that processes each specified column.

Random Selection: Within each column loop, it uses random number generation to pick unique names while ensuring no name gets picked more than once.

Output: Finally, the selected names are transferred to the "Tirage" sheet starting from the defined cell.

Conclusion

With this VBA code, you can easily automate the process of randomly selecting names from multiple columns in Excel. No more manual effort—just run the macro and let it do the work for you!

If you need any further clarifications or modifications to this code, feel free to reach out—happy coding!