VBA Express : Excel - Working with multi-selections

Excel

Working with multi-selections

Ease of Use

Easy

Version tested with

2000

Submitted by:

johnske

Description:

After running the code different parts of the worksheet will be simultaneously selected.

Discussion:

Some are unaware that various parts of a worksheet can be selected and worked on simultaneously. For instance, a procedure may require you to search for a particular value in some selected regions of the worksheet while ignoring the same value in other regions. Such a procedure is often written in the form: Select region A and search, now select region B and search, now select region C and search,....etc. With multi-select this can all be done in one go. The following code gives some basic examples of this to amuse yourself with.

Code:

instructions for use

Option Explicit Sub MultiSelect() Dim Cell As Range 'Multi-selection - separate the selection ranges with commas Range("A1:A19, A1:G3, A17:G19, C3:C9, C11:C17, E3:E17, " & _ "G1:G19, I2:I11, I10:M11, I13:M19, M2:M11").Select 'We'll add something & do a search within the selection MsgBox "This is the selection, we'll now add some values" & vbLf & _ "(x) and search the selection for these values" Range("F18, M18, H1:H19") = "x" 'Now search the selected values only For Each Cell In Selection If Cell Like "*x*" Then MsgBox "An ''x'' was found at " & Cell.Address Next Cell 'do something with the selection MsgBox "(The selection values will now be changed)" Range("H1:H19").ClearContents Selection = "HELLO" MsgBox "The selection values have all been changed" 'Clear the selection MsgBox "The selection values will now be cleared" Selection.ClearContents End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/Module
Copy and paste the code into the Module
Now select File/Close and Return To Microsoft Excel
Select Tools/Macro/Macros.../MultiSelect/Run
Now follow the prompts

Test the code:

Select Tools/Macro/Macros.../MultiSelect/Run

Sample File:

MultiSelect.zip 9.39KB

Approved by mdmackillop

This entry has been viewed 206 times.