When working with macros in Excel, named ranges can be extremely helpful in making your code more readable and easier to maintain. By using named ranges, you can make your code more self-documenting and avoid hard-coding cell references.
There are two ways to create a named range in Excel:
Sub Test() Dim myRange As Range ' Use a named range. Set myRange = Range("MyRange") ' Use an absolute cell reference. Set myRange = Range("A1:B10") ' Use a relative cell reference. Set myRange = Range("A1", Range("B1")) ' Use an intersection of two named ranges. Set myRange = Range("MyRange1").Intersect(Range("MyRange2")) ' Use a union of two named ranges. Set myRange = Union(Range("MyRange1"), Range("MyRange2")) ' Use an array constant. Set myRange = Range(Array("A1", "B1", "C1", "D1")) End Sub
Sub Test() Dim myCell As Range ' Use a named range in a formula. Set myCell = Range("A1") myCell.Formula = "=SUM(MyRange)" End Sub
Sub Test() Dim myCell As Range ' Refer to a named range on another worksheet. Set myCell = Worksheets("Sheet2").Range("A1") End Sub
Sub Test() Dim myCell As Range ' Refer to a named range in another workbook. Set myCell = Workbooks("Workbook2").Worksheets("Sheet1").Range("A1") End Sub
Sub Test() Dim myName As Name ' Delete a named range. For Each myName In ActiveWorkbook.Names If myName.Name Like "My*" Then ' Change My* to match your naming convention. ActiveWorkbook.Names(myName).Delete ' or use .Delete shift:=xlUp/xlToLeft/etc... as desired (see help) end if Next End Sub