Macros in Excel : Selecting Multiple Ranges in Excel VBA

Selecting multiple ranges in Excel VBA helps your code to work faster. You can select multiple ranges in single line code and perform the action you want to perform.

Normally we code a range by writing it within "" as under

Range("A1:A10").Select 

However if you put , between multiple ranges within the same line of the code you can select multiple ranges as given under

Range("A1:A10,D1:D10,F1:F10").Select 

How to use Multiple Ranges in Excel VBA

Following macro code will explain you how to use multiple ranges in Excel VBA

Sub Multiple_ranges()
Range("A1:A10").Select                                                                                                       
MsgBox ("Single Range Selected")
Range("A1:A10,D1:D10,F1:F10").Select
MsgBox ("Multiple Ranges Selected")
Selection.Copy
Range("A11").Select
ActiveSheet.Paste
End Sub 

In the above macro we have selected range three different ranges

A1:A10
D1:D10
F1:F10

After selection we have copied the contents to Range A11. Another option for doing this was to copy the contents one by one for each of the range. However copy and paste is only one of the example you can use this for any desired action by you.

You can combine multiple ranges into one Range object using the Union method.

The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C3:D4, and then formats the combined ranges as bold.

Sub MultipleRange() Dim r1, r2, myMultipleRange As Range Set r1 = Sheets("Sheet1").Range("A1:B2") Set r2 = Sheets("Sheet1").Range("C3:D4") Set myMultipleRange = Union(r1, r2) myMultipleRange.Font.Bold = True End Sub

Macros in Excel : Forcing Users to Enable Macros

Many times we face issue of disable macros situation while the user is using our project. Since the macros are disabled, there is no way that the macros in your project will work the dessired way. However you can force your users to enable macros while working with your files. Simplest way is to hide the main sheet in your project using xlVeryHidden property in your macro. The workseets hidden by using this method can be made visible only by using VBA. The unhide workseet dialog box does not display the names of sheets hidden by using VBA with xlVeryHidden property.



You need to have one sheet which displays message that macros are disabled and needs to be enabled to use this file. Once the macros are enabled, the message sheets can be hidden and data sheets can be made visible. You can copy the following code to Thisworkbook section of your VBA project.

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
    Sheets("Msg").Visible = True
    Sheets("Data").Visible = xlVeryHidden

End Sub

Private Sub Workbook_Open()

    Sheets("Data").Visible = True
    Sheets("Msg").Visible = xlVeryHidden

End Sub

First macro code makes Msg Sheet visible and hides Data Sheet. This macros runs on even of closing the workbook. Second macro makes Data Sheet Visible and hides Msg Sheet. This macro runs on even of opening of workbook.

You can add following line to first macro to compulsoraly save the workbook before closure. However use this only if you are sure that it is okay to save the file before closing.

   ActiveWorkbook.Save

In case you have multiple sheets and you want to hide all the sheets except the Msg sheet, use following code in ThisWorkbook Module. Make sure that the Msg sheet is first sheet of your workbook. Following code will keep first sheet visible on close and Unhide all other sheets and hide first sheet on open.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
On Error Resume Next
myCount = Application.Sheets.Count

Sheets(1).Visible = True
Range("A1").Select
For i = 2 To myCount
Sheets(i).Visible = xlVeryHidden
If i = myCount Then
End If
Next i
ActiveWorkbook.Save

End Sub

Private Sub Workbook_Open()

Dim myCount 'This line of code is optional
Dim i 'This line of code is optional
On Error Resume Next

myCount = Application.Sheets.Count
For i = 2 To myCount
Sheets(i).Visible = True
If i = myCount Then
Sheets(1).Visible = xlVeryHidden
End If
Next i

End Sub 

Download file with Macro code to Force users to Enable Macros

Code above is simplest way to force normal users to enable macros and make your project file work the way you want them to. However the advanced users can always get into your project by using VBA routine stored in onother file. This can be done by following the steps as given below.

1. Open the project file while the Macros are disabled.
2. Encable the Macros.
3. Unhide the hidden sheets in project file by macros code in another file

There are ways and means to overcome this situation also, but a advanced user will find a way to get into your project if they realy want to. So I feel there is no need to complecate the code for keeping them away. You can always make normal users to use your project file the way you want them to by using simpe code given above