Saturday, July 4, 2015

VBA Macro to Select Multiple Ranges in Excel

How to Select Multiple Ranges , Rows , Columns in Excel with VBA
Sub Select_Multi_Rows_Cols_Ranges()

Dim WS As Worksheet

Set WS = ActiveSheet

'Selecting a Row 5
WS.Rows(5).Select

'Selecting a Rows 5 to 10
WS.Rows("5:10").Select

'Selecting Multiple Rows 5,7,9
WS.Range("5:5,7:7,9:9").Select

'Selecting Multiple Rows 5,7,9 by their Range_Names
WS.Range("Row_5,Row_7,Row_9").Select

'Selecting a Column 2
WS.Columns(2).Select
WS.Columns("B:B").Select

'Selecting a Columns 1 to 3
WS.Columns("A:C").Select

'Selecting Multiple Columns 1,3,5
WS.Range("A:A,C:C,E:E").Select

'Selecting Multiple Columns 1,3,5 by their Range_Names
WS.Range("Col_1,Col_3,Col_5").Select

'Selecting Multiple Ranges <A1:C5>,<B6:D10>
WS.Range("A1:C5,B6:D10").Select

'Selecting Multiple Ranges by their Range_Names
WS.Range("Range_123,Range_345").Select

'Defining Multiple Ranges
Set Rng1 = WS.Range("A2:B5")
Set Rng2 = WS.Range("D2:E5")
Set Rng3 = WS.Range("G2:H5")

'Union all the Ranges
Set Multi_Range = Union(Rng1, Rng2, Rng3)
Multi_Range.Select
Selection.Copy

WS.Range("K2").Select
WS.Paste

'Some Pastespecial Methods
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

'Copy Pastes Method in Single Line
MyRng.Copy Destination:=Range("A2")
End Sub

Thanks,
TAMATAM

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose we have a sample Sales data, which is stored with Monthly Snapsh...

Popular Posts from this Blog