Saturday, 4 July 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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts