Logicwurks Home Page

Links To Excel Code Examples

Tracing VBA Statements
Range/Wkb/Wks Variables
Add Grand Totals Using Ranges
Using Range Offset Property
Using Range Find Method
ConvertCellAddressToRange
Set Conditional Formatting
Union Of Ranges
Parse Range Strings
Delete Duplicate Rows
Delete Rows And Columns
Worksheet Variables
TypeName And TypeOf
Loop Through Worksheets
Loop Through Open Workbooks
Form Button Magic
Command Button Magic
Add Worksheets Dynamically
ImportExternalWorksheets
Find Last Row Or Column
Copy And Paste Special
Copy To Specific Cell Types
Range Copy With Filter
ExcelFileOpenSaveClose
ExcelFileOpenSaveCSV
Open An Excel File
Open An Excel File w/Params
Open An Excel File On Web
Save A Workbook
Save A Workbook Using mso
Clone A Workbook
Test If WEB URL Exists
Parse Using Split Command
Using Classes in Excel
TypeStatementStructures
Color Management
Convert Cell Color To RGB
Sort Methods 2003 - 2010
Sort Alpha/Numeric In ASCII
Search Using Match Function
Search Using Vlookup Function
Search Using Xlookup Function
Using Find Instead of Vlookup
Remove String Non-Printables
Auto_Open And Auto_Close
Initialize Form At Open
Edit Numerics In UserForm
Load Combo And List Boxes
Floating Sheet Combo Boxes
Advanced User Form Coding
Excel Events
Worksheet Change Events
Binary Search Of Array
Typecast Constants
Excel Error Handling
Handling Optional Parameters
Data Validation Drop Downs
Insert Data Validation Sub
Read A Text File w/Handle
Write A Text File w/Handle
Read a Binary File w/Handle
Update a Binary File w/Handle
Binary File Copy and Update
Read A Text Fiile w/Script
Text File Processing Examples
Test For Exists Or Open
Splash Screen
Dynamically Load Formulas
PaymentStreamsByDate
Date Examples
Date Find Same Days
Convert Month To Number
Initialize Arrays
Load Arrays Using Evaluate
ChartsAndGraphsVBA
Redim An Array
Reassign Button Action
Timer Functions
Legacy Calendar Control
Excel 2010 Date Picker
Date Picker Alternative
Generate Multiple Worksheets
Read Access Data Into Excel
Send Outlook Email w/Attach
Copy AutoFilters To Sheets
Export A Text File
Get Windows User Name
VBA Format Statement
Manipulate Files via VBA
Dynamically Load Images
Loop Through Worksheet Objects
Loop Through Form Objects
Loop Through Files with DIR
Active-X Checkboxes
Add Forms Checkboxes Dynam
Paste Pictures Into Excel
Copy Pictures Sheet To Sheet
Copy Pictures Sheet To Sheet
Create Forms Buttons With VBA
Extract Filename From Path
Convert R1C1 Format to A1
Special Cells Property
Insert Cell Comments

Links To Access Code Examples

DAO Versus ADODB
SearchVBACodeStrings
Interface Excel With Access
Create Form Manually
Create Recordset With AddNew
Multi-Select List Boxes
Update Field(s) In A Recordset
Update Excel Pivot From Access
Import A Tab Delimited File
Export Excel FileDialog
Create Excel Within Access
Open Excel Within Access
Open Excel OBJ From Access
Format Excel From Access
Control Excel via Access VBA
On Error Best Practices
Import Tab Delim w/WinAPI
Initialize Global Variables
Using TempVars For Globals
Access Error Handling
Loop Through Form Controls
Insert A Calendar Control
Create A Filtered Recordset
Populate Combo Boxes
Bookmarks And Forms
Combo Box Multiple Sources
Passing Form Objects
Create VBA SQL Statements
Create Dynamic Queries
Display File Images On A Form
Manipulate Files via VBA
Manipulate Files via Scripting
Number Subform Records
Reference Subform Objects
Parse Delimited Fields
Parameterized Queries (VBA)
Manipulating QueryDefs In VBA
FindFirst On Combined Keys
Dlookup Command
Dlookup In Form Datasheet
Execute SQL Delete Records
Commit Form To Table
Report With No Data
Reference Form Objects
DSNLess Connections To MySQL
Print Active Form Record
Count Records in Linked Tables
Delete Empty Tables
Open Linked SQL Tables

 

Using Excel Range Variables


If you are serious about learning ranges, this book is a must!!

Power Up Using Excel Ranges


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