Sunday, May 10, 2009

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

16 comments:

  1. Thanks for putting this types of tips...
    really saves a lot of time..

    ReplyDelete
  2. hi, i have problem to select more 20 different range. for example. i have variable strR = "A1215:S1215,A1256:S1256,A1292:S1292,A1328:S1328,A1369:S1369,A1405:S1405,A1434:S1434,A1470:S1470,A1506:S1506,A1542:S1542,A1574:S1574,A1610:S1610,A1646:S1646,A1682:S1682,A1718:S1718,A1754:S1754,A1783:S1783,A1812:S1812,A1844:S1844,A1876:S1876,A1912:S1912,A1948:S1948" and i want to select all these range using range(strR).select but there is error. actually on that string there are 22 different range. but if i select under 20 different range there is no error. can anyone help me?

    ReplyDelete
    Replies
    1. Have you got any solution on this? I am having similar issue.

      Delete
  3. Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, r As Range
    Set rng = Intersect(Target, Range("a2:a" & Rows.Count))
    If rng Is Nothing Then Exit Sub
    For Each r In rng
    If Not IsEmpty(r.Value) Then
    r.Copy Destination:=Sheets("sheet2").Range("a2")
    End If
    Next
    Set rng = Nothing
    End Sub


    Hello Friends, the above program i need b2:b copies to b2 in next sheet and c2:c copies to c2 etc.... can anyone help me?

    ReplyDelete
  4. Hello, Yogesh Gupta and guys. The program of Yogesh I liked, but I want something similar.
    I need use "Union" but for 2 range of 2 different worksheet.
    I mean:
    Set r1 = Sheets("Sheet1").Range("A1:B2")
    Set r2 = Sheets("Sheet2").Range("C3:D4")
    And now I need to use "Union" for those two ranges of Sheet1 and Sheet2.

    ReplyDelete
  5. Hello, friends.

    Another idea might be:
    1. Copy the information from each worksheet and attach (paste) it to a third worksheet.
    2. Print the third worksheet.
    3. Delete the third worksheet, if possible.

    Is it possible that this can be done?

    ReplyDelete
    Replies
    1. Couldnt you just create a hidden third sheet which mirrors the data in needed format for printing, then macro-print it without having to unhide it?

      Delete
  6. Hi Yogesh...

    can u help me with this regard

    i have a summary sheet... on a daily basis i will enter in the summary sheet... i have used sumproduct formulae in the respective months..separate sheets for separate months.. the data will be automatically be updated.. so if i enter tomo becoz of the formula the data on the previous date will not exist.. so i need a macro to paste spl values the data on the respective date in the respective month sheet based on the date entered in the summary sheet..

    ReplyDelete
  7. Additional tip:

    Make sure that the number of characters in the Range parameter doesn't exceed 255 characters.

    Peace.

    Joel "2 x NBA Champion" Anthony

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hi,

    I need to know how to combine two files into one macro without the range, i mean that if the data differs in both files.

    ReplyDelete
  10. Hi,

    I need to know how to combine two files into one macro without the range, i mean that if the data differs in both files.

    ReplyDelete
  11. I have 5 values in Sheet1 (sno., name, month, week, day and one variable value)
    want to copy variable value in Sheet2 based on sno., name, month, week, day ?
    tried to copy with command button but couldn't find how to select multiple range values

    ReplyDelete
  12. hi,
    i need to know how to copy the contents present in one sheet column wise and paste the data into the different sheets columnwise(i need to increment both column data while copying and increment the sheets while pasting it)

    ReplyDelete
  13. Hi guys and gals

    I am trying to work out how to calculate the GST (for those who don’t know) using VBA

    Points to note:

    Sheet(“Data”) holds the list of items, the GST free price and if GST is applicable (some items do not attract GST here in Australia i.e. Bread Where a Cake (giggle) does attract GST.
    Sheet(“Invoice data”) column J (or #10) is where I want the GST component to be listed if applicable.
    GST is fixed at 10%

    See image for the layouts for “Data” and "Invoice data” respectively

    I want J (#10) to be just the 10% GST component and not the whole amount including GST depending if the item is attracting GST, and $0 if it does not, in the case of the bread

    Can any of you Demi Gods able to help?

    Ps anyone know how to add an image?

    ReplyDelete