Using Excel Range Variables
If you are serious about learning ranges, this book is a must!!
Range variables define a set of cells. VBA code that refers to a range variable can manipulate the contents and properties of the "cell set" defined by the range variable. An important property of range variables is that they are associated with a specific workbook and worksheet. If these associations are not specifically identified when the range variable is defined, then the definition of the range variable will use the active workbook and active worksheet as implicit associations.
The code below show how to specifically assign a range variable to a workbook/worksheet. This illustration shows the many forms that you can use to define and manipulate range variables and the data that is associated with the ranges.
If you need to refer to "Named Ranges" in an Excel workbook (Range Names created outside the VBA environment) then refer to:
Referencing Named Ranges in VBA
Program Code
Option Explicit
Public Sub FormsOfCellReference()
Dim wkbMyWorkbook As Workbook
Dim wksMyWorksheet As Worksheet
Dim strSheetName As String
Dim strWorkbookName As String
Dim strRange As String
Dim rngMyRange1 As Range
Dim rngMyRange2 As Range
Dim rngMyRange3 As Range
Dim rngMyRange4 As Range
Dim rngMyRange5 As Range
Dim rngMyRange6 As Range
Dim rngMyRange7 As Range
Dim rngMyRange8 As Range
Dim rngMyRange9 As Range
Dim rngMyRange10 As Range
Dim rngMyRange11 As Range
Dim rngMyRange12 As Range
Dim rngMyRange20 As Range
' ******************************************************
' Set Up String Variables
' ******************************************************
strWorkbookName = "CellAndRangeReferences.xls"
strSheetName = "Sheet1"
' ******************************************************
' Set Workbook and Worksheet Variables
' ******************************************************
Set wkbMyWorkbook = Workbooks("CellAndRangeReferences.xls") ' By Name in Literal
Set wkbMyWorkbook = Workbooks(strWorkbookName) ' By Name in Variable
Set wkbMyWorkbook = ThisWorkbook ' By Workbook That Contains This Code
Set wkbMyWorkbook = ActiveWorkbook ' By Currently Open And Active Workbook
Set wksMyWorksheet = wkbMyWorkbook.Sheets("Sheet1") ' By Name with Literal
Set wksMyWorksheet = wkbMyWorkbook.Sheets(strSheetName) ' By Name In Variable
Set wksMyWorksheet = wkbMyWorkbook.Sheets(1) ' By Position in Collection
Set wksMyWorksheet = wkbMyWorkbook.ActiveSheet ' By Currently Active Sheet
' ******************************************************
' Ranges Are Associated With Worksheets
' Demonstrate Many Forms Of Range Selection
' ******************************************************
Set rngMyRange1 = _
wksMyWorksheet.Range("A2,B3,C4,E5") ' Select Individual Cells
Set rngMyRange2 = _
wksMyWorksheet.Range("$E:$F,$A$2,$A$15:$B$20,$5:$11") ' Select Entire Columns, Cells, And Entire Rows
Set rngMyRange3 = _
wkbMyWorkbook.Sheets(wksMyWorksheet.Name).Range("A1") ' Select Single Cell
Set rngMyRange4 = _
wksMyWorksheet.Range("E:F,A2,A15:B20,5:11") ' Select Cells, Rows and Columns
Set rngMyRange5 = _
wksMyWorksheet.Range("A1,E:E,5:5") ' Select Single Row, Single Column and Cell
Set rngMyRange6 = Range(wksMyWorksheet.Cells(1, 1), _
wksMyWorksheet.Cells(5, 5)) ' Select Cell Range using Cell Notation
Set rngMyRange7 = Range(wksMyWorksheet.Cells(10, 1), _
wksMyWorksheet.Cells(20, 5)) ' Select Cell Range using Cell Notation
Set rngMyRange8 = Union(rngMyRange6, rngMyRange7) ' Combine Two Ranges with Union Method
Set rngMyRange9 = Range("MyNamedRange") ' Use Worksheet Named Range
Set rngMyRange9 = Union(Range("K:L,5:6"), Range(Cells(1, 1), Cells(2, 2)), Range("8:8")) 'Mixing Formats
rngTest.Select
strRange = "A2,B3,C4,D6,E8"
Set rngMyRange10 = wksMyWorksheet.Range(strRange) ' Select Using String Range
Set rngMyRange11 = _
Range(wksMyWorksheet.Columns(2), _
wksMyWorksheet.Columns(5)) ' Select A Column Range
Set rngMyRange12 = _
Range(wksMyWorksheet.Rows(5), _
wksMyWorksheet.Rows(10)) ' Select A Row Range
End Sub
_________________________________________________________________________________________
' **********************************************************************
' Exotic Range Examples
' **********************************************************************
Option Explicit
Public Sub ExoticRangeFormats()
Dim wkbThisWorkbook As Workbook
Dim wksSheet1 As Worksheet
Dim wksSheet2 As Worksheet
Dim rngTest1 As Range
Dim rngTest2 As Range
Dim lngColumnNumber As Long
Set wkbThisWorkbook = ThisWorkbook
Set wksSheet1 = wkbThisWorkbook.Sheets("Sheet1")
Set wksSheet2 = wkbThisWorkbook.Sheets("Sheet2")
' **********************************************************************
' The Following Illustrates How To Comingle Alpha Ranges
' With Cell References
' **********************************************************************
Set rngTest1 = Union(wksSheet1.Range("K:L,5:6"), Range(wksSheet1.Cells(1, 1), wksSheet1.Cells(2, 2)), wksSheet1.Range("8:8"))
rngTest1.Select
MsgBox ("Examine Ranges Selected")
' **********************************************************************
' The Following Translates to : Range("A:B,5:5,J:J,10:11")
' This Illustrates How A String Can Be Built in One Range Statement
' **********************************************************************
wksSheet1.Cells(1, 1).Select
lngColumnNumber = 10
Set rngTest2 = wksSheet1.Range("A:B," & "5:5," & ConvertColumnNumberToLetter(lngColumnNumber) & ":" & _
ConvertColumnNumberToLetter(lngColumnNumber) & ",10:11")
rngTest2.Select
MsgBox ("Examine Ranges Selected")
End Sub
' **********************************************************************
' Convert A Column Number to a Letter
' **********************************************************************
Public Function ConvertColumnNumberToLetter(ColumnNumber As Long) As String
If ColumnNumber > 26 Then
ConvertColumnNumberToLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
ConvertColumnNumberToLetter = Chr(ColumnNumber + 64)
End If
End Function
_________________________________________________________________________________________
Option Explicit
Sub ColumnExamples()
' ***************************************************************
' Examples On How To Use The Columns Property
' Note: Normally The Cells Property Requires A Preface of
' the worksheet variable, but when the .Address
' property is used, the worksheet variable in front of
' the Cells statement is ignored in liew of the
' worksheet variable in front of the column statement.
' ***************************************************************
Dim wkbThisWorkbook As Workbook
Dim wksSheet1 As Worksheet
Dim wksSheet2 As Worksheet
Dim rngColumnRange As Range
Dim rngColumnRange1 As Range
Dim rngColumnRange2 As Range
' ***************************************************************
' Workbook and Worksheet Variables
' ***************************************************************
Set wkbThisWorkbook = ThisWorkbook
Set wksSheet1 = wkbThisWorkbook.Sheets("Sheet1")
Set wksSheet2 = wkbThisWorkbook.Sheets("Sheet2")
' ***************************************************************
' Set A Single Column Range (Notice Multiple Formats)
' ***************************************************************
Set rngColumnRange = wksSheet1.Columns("C:C")
Set rngColumnRange = wksSheet1.Columns("D")
Set rngColumnRange = wksSheet2.Columns(5)
' ***************************************************************
' Set A Contiguous Column Range
' ***************************************************************
Set rngColumnRange = wksSheet1.Columns("D:F")
' ***************************************************************
' Set A Single Column Range Using Cell Addresses
' ***************************************************************
Set rngColumnRange = wksSheet2.Columns(Cells(5, 7).EntireColumn.Address)
' ***************************************************************
' Set Contiguous Column Ranges Using Cell Addresses
' ***************************************************************
Set rngColumnRange = Range(wksSheet1.Columns(Cells(5, 7).EntireColumn.Address), wksSheet1.Columns(Cells(5, 9).EntireColumn.Address))
' ***************************************************************
' Set Two Non-Contiguous Columns
' ***************************************************************
Set rngColumnRange1 = wksSheet2.Columns(Cells(5, 7).EntireColumn.Address)
Set rngColumnRange2 = wksSheet2.Columns(Cells(5, 9).EntireColumn.Address)
Set rngColumnRange = Union(rngColumnRange1, rngColumnRange2)
' ***************************************************************
' Hide And UnHide Columns
' ***************************************************************
wksSheet1.Columns("C:C").EntireColumn.Hidden = True
wksSheet1.Columns("B:J").EntireColumn.Hidden = False
' ***************************************************************
' Hide Columns G Through I
' ***************************************************************
Range(wksSheet2.Columns(Cells(5, 7).EntireColumn.Address), wksSheet2.Columns(Cells(5, 9).EntireColumn.Address)).EntireColumn.Hidden = True
wksSheet1.Columns("B:J").EntireColumn.Hidden = False
' ***************************************************************
' Hide Columns G Only
' ***************************************************************
wksSheet1.Columns(Cells(5, 7).EntireColumn.Address).EntireColumn.Hidden = True
End Sub
_________________________________________________________________________________________
Sub RowExample()
' ***************************************************************
' Examples On How To Use The Rows Property
' ***************************************************************
Dim wkbThisWorkbook As Workbook
Dim wksSheet1 As Worksheet
Dim wksSheet2 As Worksheet
Dim rngRowRange As Range
Dim rngRowRange1 As Range
Dim rngRowRange2 As Range
' ***************************************************************
' Set Workbook And Worksheet Variables
' ***************************************************************
Set wkbThisWorkbook = ThisWorkbook
Set wksSheet1 = wkbThisWorkbook.Sheets("Sheet1")
Set wksSheet2 = wkbThisWorkbook.Sheets("Sheet2")
' ***************************************************************
' Set A Single Row Range (Notice Multiple Formats)
' ***************************************************************
Set rngRowRange = wksSheet1.Rows("1:1")
Set rngRowRange = wksSheet1.Rows("2")
Set rngRowRange = wksSheet2.Rows(3)
' ***************************************************************
' Set A Contiguous Row Range
' ***************************************************************
Set rngRowRange = wksSheet2.Rows("1:5")
' ***************************************************************
' Set A Single Row Range Using Cell Addresses
' ***************************************************************
Set rngRowRange = wksSheet2.Rows(Cells(5, 7).EntireRow.Address)
' ***************************************************************
' Set Contiguous Row Ranges Using Cell Addresses
' ***************************************************************
Set rngRowRange = Range(wksSheet1.Rows(Cells(5, 7).EntireRow.Address), wksSheet1.Rows(Cells(8, 9).EntireRow.Address))
' ***************************************************************
' Set Two Non-Contiguous Rows
' ***************************************************************
Set rngRowRange1 = wksSheet2.Rows(Cells(5, 7).EntireRow.Address)
Set rngRowRange2 = wksSheet2.Rows(Cells(8, 9).EntireRow.Address)
Set rngRowRange = Union(rngRowRange1, rngRowRange2)
' ***************************************************************
' Hide And UnHide Rows
' ***************************************************************
wksSheet1.Rows("2:2").EntireRow.Hidden = True
wksSheet2.Rows("1:10").EntireRow.Hidden = False
' ***************************************************************
' Hide Rows 5 Through 7
' ***************************************************************
Range(wksSheet1.Rows(Cells(5, 7).EntireRow.Address), wksSheet1.Rows(Cells(7, 9).EntireRow.Address)).EntireRow.Hidden = True
wksSheet1.Rows("1:10").EntireRow.Hidden = False
' ***************************************************************
' Hide Row 5 Only
' ***************************************************************
wksSheet1.Rows(Cells(5, 7).EntireRow.Address).EntireRow.Hidden = True
End Sub
_________________________________________________________________________________________
' ****************************************************************************
' Next Example
' ****************************************************************************
Option Explicit
Dim CheckName As String
Public Sub TestOfRangeVariable()
' *************************************************
' Range Variables
' *************************************************
Dim rngSheet1 As Range
Dim rngSheet2 As Range
Dim rngSheet3 As Range
Dim rngOtherWorkbook As Range
' *************************************************
' Workbook And Worksheet String Variables
' *************************************************
Dim strThisWorkbookName As String
Dim strThisWorksheetName As String
Dim strSpreadsheetRange As String
' *************************************************
' Workbook And Worksheet Variables
' *************************************************
Dim wkbWorkbook1 As Workbook
Dim shtSheet1 As Worksheet
' *************************************************
' Activate The Workbook Associated With This Code
' *************************************************
ThisWorkbook.Activate
' ***************************************************
' Three Ways To Set String Variable to Workbook Name
' ***************************************************
strThisWorkbookName = "ExcelRangeVariablesTester.xls"
strThisWorkbookName = ActiveWorkbook.Name
strThisWorkbookName = ThisWorkbook.Name
' *************************************************
' Four Ways To Set A Workbook Variable
' *************************************************
Set wkbWorkbook1 = Workbooks("ExcelRangeVariablesTester.xls")
Set wkbWorkbook1 = Workbooks(strThisWorkbookName)
Set wkbWorkbook1 = ThisWorkbook
Set wkbWorkbook1 = ActiveWorkbook
' ***************************************************
' Two Ways To Set String Variable to Worksheet Name
' ***************************************************
strThisWorksheetName = ActiveSheet.Name
strThisWorksheetName = "Sheet1"
' *************************************************
' Four Ways To Set A Worksheet Variable
' *************************************************
Set shtSheet1 = ActiveWorkbook.ActiveSheet
Set shtSheet1 = ThisWorkbook.ActiveSheet
Set shtSheet1 = wkbWorkbook1.Sheets("Sheet1")
Set shtSheet1 = wkbWorkbook1.Sheets(strThisWorksheetName)
' *************************************************
' Samples of Worksheet Selection
' *************************************************
wkbWorkbook1.Sheets("Sheet2").Select
Workbooks(strThisWorkbookName).Sheets("Sheet3").Select
ThisWorkbook.Sheets("Sheet2").Select
ActiveWorkbook.Sheets("Sheet1").Select
shtSheet1.Select
' *************************************************
' Select A Cell on the Selected Worksheet
' *************************************************
Cells(1, 1).Select
' *************************************************
' Set A Range Variable Using A Worksheet Variable
' *************************************************
' Note That When A Worksheet Variable Has Been
' Set, You Don't Refer to the Workbook Because
' It is Implied by The Worksheet Definition
' *************************************************
Set rngSheet1 = Range(shtSheet1.Cells(1, 1), shtSheet1.Cells(5, 5))
rngSheet1.Select
' ********************************************************
' Set A Range When Worksheet Variable Has Not Been Defined
' ********************************************************
' In This Case, You Do Refer To the Worksheet Since It Is
' Not Implied
' ********************************************************
Set rngSheet2 = Range(wkbWorkbook1.Sheets("Sheet2").Cells(3, 3), wkbWorkbook1.Sheets("Sheet2").Cells(3, 10))
Set rngSheet3 = Range(wkbWorkbook1.Sheets("Sheet3").Cells(5, 5), wkbWorkbook1.Sheets("Sheet3").Cells(10, 10))
Set rngOtherWorkbook = Range(Workbooks("ExcelRangeTest.xls").Sheets("Sheet1").Cells(1, 1), _
Workbooks("ExcelRangeTest.xls").Sheets("Sheet1").Cells(20, 20))
' ********************************************************
' Switch Back To Sheet1 To Prove Range Variables Are
' Assigned to Workbook/Worksheet Regardless of Current
' Selected Workbook or Worksheet
' ********************************************************
ThisWorkbook.Sheets("Sheet1").Select
' ********************************************************
' Push Values Into A Range
' ********************************************************
rngSheet2.Value = "Hello"
rngSheet3.Value = "Yes"
rngOtherWorkbook.Value = "New"
' *****************************************************
' Note: To Select A Sheet, The Workbook Must Be Active
' *****************************************************
shtSheet1.Select
' *************************************************
' Delete An Actual Spreadsheet Named Range
' *************************************************
Call DeleteSpreadsheetRange
' *************************************************
' Create An Actual Spreadsheet Range
' *************************************************
strSpreadsheetRange = "='" & ActiveSheet.Name & "'!R1C1:R40C3"
ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=strSpreadsheetRange
Range("MyRange").Select
End Sub
' *************************************************
' Delete A Range Name (Not The Contents)
' *************************************************
Sub DeleteSpreadsheetRange()
On Error GoTo RangeError
CheckName = Range("MyRange").Address
ActiveWorkbook.Names("MyRange").Delete
Exit Sub
RangeError:
Resume Next
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% A SECOND EXAMPLE %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Here's Another Example Of A Program Using Range Variables For A Copy And Paste Special
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Option Explicit
Option Base 1
Dim strCancel As String
' ************************************************
' Variables For File Open Dialogue Box
' ************************************************
Dim strDialogueFileTitle As String
Dim strFilt As String
Dim intFilterIndex As Integer
Dim strFreightWorkbookNameAndPath As String
Dim strFreightImportWorkbookName As String
Dim strFreightImportWorksheetName As String
Dim strImportedWorkbookNameAndPath As String
' ************************************************
' Workbook And Worksheet Variables
' ************************************************
Dim wkbTemplate As Workbook
Dim shtTemplate As Worksheet
Dim wkbImport As Workbook
Dim shtImport As Worksheet
' *************************************************************
' Variables Used To Scan Each Worksheet in the Freight Workbook
' *************************************************************
Dim lngSheetNumber As Long
Dim lngLastRow As Long
Dim shtCurrentSheet As Worksheet
Dim rngScanWorksheetRows As Range
Dim C As Range
Dim intLastOrderColumn As Integer
Dim lngTemplateRow As Long
Dim i As Integer
Public Sub FormatFreight()
' ********************************************************
' Turn Off Screen Updating
' ********************************************************
Application.ScreenUpdating = False
' ****************************************************************************
' Record Current Workbook and Worksheet Information
' ****************************************************************************
Set wkbTemplate = ThisWorkbook
Set shtTemplate = ThisWorkbook.ActiveSheet
' ****************************************************************************
' Set Up Filters For Which Files Should Show In The Open File Dialog Box
' ****************************************************************************
strFilt = "Excel Files (*.xlsx),*.xlsx," & _
"Legacy Excel Files (*.xls),*.xls,"
' ****************************************************************************
' Set Up The Prompt In The Dialogue Box
' ****************************************************************************
intFilterIndex = 1
strDialogueFileTitle = "Select The 2008 Freight Worksheet"
' ****************************************************************************
' Present the Open File Dialogue To The User
' ****************************************************************************
Call OpenFileDialogue
' ****************************************************************************
' Notify The User If No File Was Successfully Opened
' ****************************************************************************
If strCancel = "Y" Then
MsgBox ("An Open Error Occurred Importing The Feight Workbook")
Exit Sub
End If
' ****************************************************************************
' Make Sure It Is The Correct Workbook
' ****************************************************************************
If Cells(1, 1).Value <> "Shipping Number" Then
MsgBox ("The Input File Does Not Match Expected Values")
Exit Sub
End If
' ********************************************************
' Save The New Workbook And Worksheet Names
' ********************************************************
strFreightImportWorkbookName = ActiveWorkbook.Name
strFreightImportWorksheetName = ActiveSheet.Name
Set wkbImport = ActiveWorkbook
Set shtImport = ActiveSheet
lngTemplateRow = 3
' ***********************************************************************
' Scan Through Each Worksheet And Pull In The Data To The Template
' ***********************************************************************
For lngSheetNumber = 1 To Sheets.Count
Set shtCurrentSheet = Sheets(lngSheetNumber)
lngLastRow = shtCurrentSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set rngScanWorksheetRows = shtCurrentSheet.Range("A2:A" & lngLastRow)
' ***********************************************************************
' Locate The Start Of The Customer Column
' ***********************************************************************
intLastOrderColumn = 0
For i = 4 To 50
If shtCurrentSheet.Cells(1, i).Value = "CUSTOMER" Then
intLastOrderColumn = i - 1
Exit For
End If
Next i
If intLastOrderColumn = 0 Then
MsgBox ("No CUSTOMER Heading")
Exit Sub
End If
' ***********************************************************************
' Copy The Data To The Template
' ***********************************************************************
For Each C In rngScanWorksheetRows
For i = 4 To intLastOrderColumn
If C.Offset(0, i - 1).Value <> "" Then
Range(C, C.Offset(0, 2)).Copy
shtTemplate.Cells(lngTemplateRow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
C.Offset(0, i - 1).Copy
shtTemplate.Cells(lngTemplateRow, 4).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(C.Offset(0, intLastOrderColumn), C.Offset(0, intLastOrderColumn + 18)).Copy
shtTemplate.Cells(lngTemplateRow, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
lngTemplateRow = lngTemplateRow + 1
End If
Next i
Next C
' ***********************************************************************
' Prepare To Scan The Next Worksheet
' ***********************************************************************
Next lngSheetNumber
' *********************************************************
' Close The Imported Freight Data
' *********************************************************
wkbTemplate.Activate
Application.DisplayAlerts = False
wkbImport.Close Savechanges:=False
Application.DisplayAlerts = True
shtTemplate.Select
' **************************************************************
' Remove the Button For Generating the Spreadsheet
' **************************************************************
ActiveSheet.Shapes("Button 1").Select
Selection.Cut
Range("A1").Select
End Sub
Sub OpenFileDialogue()
' ************************************************
' Display a File Open Dialogue Box For The User
' ************************************************
strCancel = "N"
strImportedWorkbookNameAndPath = Application.GetOpenFilename _
(FileFilter:=strFilt, _
FilterIndex:=intFilterIndex, _
Title:=strDialogueFileTitle)
' ************************************************
' Exit If No File Selected
' ************************************************
If strImportedWorkbookNameAndPath = "" Then
MsgBox ("No Filename Selected")
strCancel = "Y"
Exit Sub
ElseIf strImportedWorkbookNameAndPath = "False" Then
MsgBox ("You Clicked The Cancel Button")
strCancel = "Y"
Exit Sub
End If
' ******************************************************
' Now That You Have The User Selected File Name, Open It
' ******************************************************
Workbooks.Open strImportedWorkbookNameAndPath
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% A THIRD EXAMPLE %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Here are a variety of ways to use the Range variable to select Columns, Rows, Cell Ranges, and Individual Cells
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Option Explicit
Sub SampleOfRangeSelection()
' ****************************************************
' This Demonstrates How To Select Entire Columns,
' Entire Rows, Cell ranges and Individual Cells
' ****************************************************
Dim wksSheet1 As Worksheet
Dim strRange As String
Dim rngFullRange As Range
Set wksSheet1 = Sheets("Sheet1")
' *****************************************************
' Select Full Columns, Cell Ranges and Individual Cells
' *****************************************************
Set rngFullRange = wksSheet1.Range("E:F,A2,A10:B20,C5")
' *****************************************************
' Create A Union Including Additional Full Rows
' *****************************************************
Set rngFullRange = Union(rngFullRange, wksSheet1.Rows("5:11"))
rngFullRange.Select
Cells(1, 1).Select
' ****************************************************
' This Format Allows Full Columns, Full Rows,
' Cell Ranges and Individual Cells To Be Selected
' ****************************************************
Range("$E:$F,$A$2,$A$10:$B$20,$5:$11").Select
' *****************************************************
' Other Examples
' *****************************************************
wksSheet1.Range("A2,B3,C4,E5").Select
strRange = "A2,B3,C4,D6,E8"
wksSheet1.Range(strRange).Select
wksSheet1.Range("E:F,A2,A10:B20,C5").Select
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% A FOURTH EXAMPLE %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' The Following exampes show how to get a "Named Range" Name from Cell (Range) Addresses
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Option Explicit
Public Sub GetRangeNameOfCells1()
Dim strRangeName As String
On Error GoTo NoRangeName
strRangeName = Range("A1:B3").Name.Name
Continue:
On Error GoTo 0
MsgBox ("Range Name Is " & strRangeName)
Exit Sub
NoRangeName:
strRangeName = ""
Resume Continue
End Sub
Public Sub FindRangeNames()
Dim objName As Name
For Each objName In Names
Debug.Print objName.Name
Debug.Print objName.RefersToRange.Address
Next objName
End Sub
Public Sub GetRangeNameOfCells2()
Dim namRangeName As Name
Dim strRangeName As String
On Error Resume Next
Set namRangeName = Nothing
Set namRangeName = Range("A1:B2").Name
On Error GoTo 0
If Not namRangeName Is Nothing Then
strRangeName = namRangeName.Name
Else
strRangeName = ""
End If
MsgBox ("Range Name Is " & strRangeName)
End Sub
Option Explicit
Public Sub IllustrateRangeStatements()
' **************************************************************************
' You Must Have This Workbook Open:
' CellAndRangeReferences.xls
' In addition to this workbook
' **************************************************************************
' **************************************************************************
' Workbook And Worksheet Variables
' **************************************************************************
Dim wkbMyWorkbook As Workbook
Dim wksMyWorksheet As Worksheet
Dim wkbOpenWorkbook As Workbook
' **************************************************************************
' String Variables
' **************************************************************************
Dim strWorkbookName As String
Dim strWorksheetName As String
' **************************************************************************
' Range Variables
' **************************************************************************
Dim rngRange1 As Range
Dim rngRange2 As Range
Dim rngRange3 As Range
Dim rngRange4 As Range
Dim rngRange5 As Range
Dim rngRange6 As Range
Dim rngRange7 As Range
Dim rngRange8 As Range
Dim rngRange9 As Range
' **************************************************************************
' Long Integer Pointers
' **************************************************************************
Dim lngRowPointer1 As Long
Dim lngRowPointer2 As Long
Dim lngColumnPointer1 As Long
Dim lngColumnPointer2 As Long
' **************************************************************************
' Boolean Pointers
' **************************************************************************
Dim boolWorkbookOpen As Boolean
' **************************************************************************
' Verify Two Workbooks Are Open
' **************************************************************************
boolWorkbookOpen = False
For Each wkbOpenWorkbook In Workbooks
If wkbOpenWorkbook.Name = "CellAndRangeReferences.xls" Then
boolWorkbookOpen = True
End If
Next wkbOpenWorkbook
If Not boolWorkbookOpen Then
MsgBox ("You Must Have Two Workbooks Open" & vbCrLf & _
"This Workbook And CellAndRangeReferences.xls")
Exit Sub
End If
' **************************************************************************
' Initialize Strings
' **************************************************************************
strWorkbookName = "CellAndRangeReferences.xls"
strWorksheetName = "Sheet1"
' **************************************************************************
' Initialize Pointers
' **************************************************************************
lngRowPointer1 = 10
lngRowPointer2 = 15
lngColumnPointer1 = 2
lngColumnPointer2 = 4
' **************************************************************************
' Set Workbook Samples
' **************************************************************************
Set wkbMyWorkbook = Workbooks("CellAndRangeReferences.xls")
Set wkbMyWorkbook = Workbooks(strWorkbookName)
Set wkbMyWorkbook = ActiveWorkbook
Set wkbMyWorkbook = ThisWorkbook
' **************************************************************************
' Set Worksheet Samples
' **************************************************************************
Set wksMyWorksheet = wkbMyWorkbook.Sheets("Sheet1")
Set wksMyWorksheet = wkbMyWorkbook.Sheets(strWorksheetName)
Set wksMyWorksheet = wkbMyWorkbook.Sheets(1)
Set wksMyWorksheet = wkbMyWorkbook.ActiveSheet
' **************************************************************************
' Using The Range Statement With Variables
' **************************************************************************
Set rngRange1 = wksMyWorksheet.Range("A1")
rngRange1.Value = "ABC"
Set rngRange2 = wksMyWorksheet.Range("A2,B3,C4,E5")
rngRange2.Value = "ABC"
Set rngRange3 = wksMyWorksheet.Range("E:G")
rngRange3.Value = "ABC"
Set rngRange4 = wksMyWorksheet.Range("E:E,G:G")
rngRange4.Value = "ABC"
Set rngRange5 = wksMyWorksheet.Range("5:11")
rngRange5.Value = "ABC"
Set rngRange6 = wksMyWorksheet.Range("5:5,11:11")
rngRange6.Value = "ABC"
Set rngRange7 = wksMyWorksheet.Range("A2:B4,C10:D15")
rngRange1.Value = "ABC"
Set rngRange8 = wksMyWorksheet.Range("A2,B3,C5,D2:E7,F:H,10:12")
rngRange1.Value = "ABC"
' **************************************************************************
' Using the Range Statement Without Variables
' **************************************************************************
wksMyWorksheet.Range("A1") = "ABC"
wksMyWorksheet.Range("A2,B3,C4,E5") = "ABC"
wksMyWorksheet.Range("E:G") = "ABC"
wksMyWorksheet.Range("E:E,G:G") = "ABC"
wksMyWorksheet.Range("5:11") = "ABC"
wksMyWorksheet.Range("5:5,11:11") = "ABC"
wksMyWorksheet.Range("A2:B4,C10:D15") = "ABC"
wksMyWorksheet.Range("A2,B3,C5,D2:E7,F:H,10:12") = "ABC"
' **************************************************************************
' Using Ranges within Ranges With Variables
' **************************************************************************
Set rngRange1 = Range(wksMyWorksheet.Cells(1, 1), wksMyWorksheet.Cells(5, 10))
rngRange1.Value = "ABC"
Set rngRange2 = Range(wksMyWorksheet.Rows(1), wksMyWorksheet.Rows(5))
rngRange2.Value = "ABC"
Set rngRange3 = Range(wksMyWorksheet.Columns(4), wksMyWorksheet.Columns(6))
rngRange3.Value = "ABC"
Set rngRange4 = Range(wksMyWorksheet.Cells(1, "A"), wksMyWorksheet.Cells(5, "C"))
rngRange4.Value = "ABC"
Set rngRange5 = Range(wksMyWorksheet.Rows("10"), wksMyWorksheet.Rows("12"))
rngRange5.Value = "ABC"
Set rngRange6 = Range(wksMyWorksheet.Columns("C"), wksMyWorksheet.Columns("E"))
rngRange6.Value = "ABC"
Set rngRange7 = Range(wksMyWorksheet.Cells(lngRowPointer1, lngColumnPointer1), wksMyWorksheet.Cells(lngRowPointer2, lngColumnPointer2))
rngRange7.Value = "ABC"
Set rngRange8 = Range(wksMyWorksheet.Rows(lngRowPointer1), wksMyWorksheet.Rows(lngRowPointer2))
rngRange8.Value = "ABC"
Set rngRange9 = Range(wksMyWorksheet.Columns(lngColumnPointer1), wksMyWorksheet.Columns(lngColumnPointer2))
rngRange9.Value = "ABC"
' **************************************************************************
' Using Ranges within Ranges Without Variables
' **************************************************************************
Range(wksMyWorksheet.Cells(1, 1), wksMyWorksheet.Cells(5, 10)) = "ABC"
Range(wksMyWorksheet.Rows(1), wksMyWorksheet.Rows(5)) = "ABC"
Range(wksMyWorksheet.Columns(4), wksMyWorksheet.Columns(6)) = "ABC"
Range(wksMyWorksheet.Cells(1, "A"), wksMyWorksheet.Cells(5, "C")) = "ABC"
Range(wksMyWorksheet.Rows("10"), wksMyWorksheet.Rows("12")) = "ABC"
Range(wksMyWorksheet.Columns("C"), wksMyWorksheet.Columns("E")) = "ABC"
Range(wksMyWorksheet.Cells(lngRowPointer1, lngColumnPointer1), wksMyWorksheet.Cells(lngRowPointer2, lngColumnPointer2)) = "ABC"
Range(wksMyWorksheet.Rows(lngRowPointer1), wksMyWorksheet.Rows(lngRowPointer2)) = "ABC"
Range(wksMyWorksheet.Columns(lngColumnPointer1), wksMyWorksheet.Columns(lngColumnPointer2)) = "ABC"
' **************************************************************************
' Using The Cells Property With Range Variables
' **************************************************************************
Set rngRange1 = wksMyWorksheet.Cells(3, 5)
rngRange1.Value = "ABC"
Set rngRange2 = wksMyWorksheet.Cells(lngRowPointer1, 10)
rngRange2.Value = "ABC"
Set rngRange3 = wksMyWorksheet.Cells(lngRowPointer1, lngColumnPointer1)
rngRange3.Value = "ABC"
Set rngRange4 = wksMyWorksheet.Cells(1, "C")
rngRange4.Value = "ABC"
Set rngRange5 = wksMyWorksheet.Cells(10)
rngRange5.Value = "ABC"
Set rngRange6 = wksMyWorksheet.Cells
rngRange6.Value = "ABC"
' **************************************************************************
' Using The Cells Property Without Range Variables
' **************************************************************************
wksMyWorksheet.Cells(3, 5) = "ABC"
wksMyWorksheet.Cells(lngRowPointer1, 10) = "ABC"
wksMyWorksheet.Cells(lngRowPointer1, lngColumnPointer1) = "ABC"
wksMyWorksheet.Cells(1, "C") = "ABC"
wksMyWorksheet.Cells(10) = "ABC"
wksMyWorksheet.Cells = "ABC"
' **************************************************************************
' Using The Rows Property With Range Variables
' **************************************************************************
Set rngRange1 = wksMyWorksheet.Rows(10)
rngRange1.Value = "ABC"
Set rngRange2 = wksMyWorksheet.Rows("10")
rngRange2.Value = "ABC"
Set rngRange3 = wksMyWorksheet.Rows("10:12")
rngRange3.Value = "ABC"
' **************************************************************************
' Using The Rows Property Without Range Variables
' **************************************************************************
wksMyWorksheet.Rows(10) = "ABC"
wksMyWorksheet.Rows("10") = "ABC"
wksMyWorksheet.Rows("10:12") = "ABC"
' **************************************************************************
' Using The Columns Property With Range Variables
' **************************************************************************
Set rngRange1 = wksMyWorksheet.Columns(5)
rngRange1.Value = "ABC"
Set rngRange2 = wksMyWorksheet.Columns("E")
rngRange2.Value = "ABC"
Set rngRange3 = wksMyWorksheet.Columns("E:G")
rngRange3.Value = "ABC"
' **************************************************************************
' Using The Columns Property Without Range Variables
' **************************************************************************
wksMyWorksheet.Columns(5) = "ABC"
wksMyWorksheet.Columns("E") = "ABC"
wksMyWorksheet.Columns("E:G") = "ABC"
' **************************************************************************
' Other Range Properties
' **************************************************************************
Selection = "ABC"
ActiveCell = "ABC"
ActiveCell.CurrentRegion = "ABC"
wksMyWorksheet.UsedRange = "ABC"
' **************************************************************************
' Named Ranges
' **************************************************************************
Range("SalesReps") = "ABC"
' **************************************************************************
' Substituting for Worksheet Variables
' **************************************************************************
ThisWorkbook.Worksheets("Sheet1").Range("A1") = "ABC"
Workbooks("ExcelRangeProject.xlsm").Worksheets("Sheet1").Range("A2") = "ABC"
End Sub