These examples show how to select the used range, which includes formatted cells that don’t contain data, and how to select a data range, which includes cells that contains actual data.
Sample code provided by: Tom Urtis, Atlas Programming Management
Selecting the Used Range
This example shows how to select the used range on the current sheet, which includes formatted cells that don’t contain data, by using the UsedRange property of the Worksheet object and the Select method of the Range.md) object. Then it displays the address of the range to the user.
“vb
Sub SelectUsedRange()
ActiveSheet.UsedRange.Select
MsgBox "The used range address is " & ActiveSheet.UsedRange.Address(0, 0) & ".", 64, "Used range address:"
End Sub
“
Selecting a Data Range Starting at Cell A1
This example shows how to select a data range on the current sheet, starting at cell A1, and display the address of the range to the user. The data range does not include cells that are formatted that don’t contain data. To get the data range, this example finds the last row and the last column that contain actual data by using the Find method of the Range.md) object.
“vb
Sub SelectDataRange()
Dim LastRow As Long, LastColumn As Long
LastRow = Cells.Find(What:="", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range("A1").Resize(LastRow, LastColumn).Select
MsgBox "The data range address is " & Selection.Address(0, 0) & ".", 64, "Data-containing range address:"
End Sub
“
Selecting a Data Range of Unknown Starting Location
This example shows how to select a data range on the current sheet when you don’t know the starting location, and display the address of the range to the user. The data range does not include cells that are formatted that don’t contain data. To get the data range, this example finds the first and last row and column that contain actual data by using the Find method of the Range.md) object.
“`vb Sub UnknownRange() If WorksheetFunction.CountA(Cells) = 0 Then MsgBox “There is no range to be selected.”, , “No cells contain any values.” Exit Sub Else Dim FirstRow&, FirstCol&, LastRow&, LastCol& Dim myUsedRange As Range FirstRow = Cells.Find(What:=”*”, SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
On Error Resume Next FirstCol = Cells.Find(What:=”*”, SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column If Err.Number <> 0 Then Err.Clear MsgBox _ “There are horizontally merged cells on the sheet” & vbCrLf & _ “that should be removed in order to locate the range.”, 64, “Please unmerge all cells.” Exit Sub End If
LastRow = Cells.Find(What:=”“, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row LastCol = Cells.Find(What:=”“, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column Set myUsedRange = Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)) myUsedRange.Select MsgBox “The data range on this worksheet is ” & myUsedRange.Address(0, 0) & “.”, 64, “Range address:” End If End Sub “`
About the Contributor
MVP Tom Urtis is the founder of Atlas Programming Management, a full-service Microsoft Office and Excel business solutions company in Silicon Valley. Tom has over 25 years of experience in business management and developing Microsoft Office applications, and is the coauthor of “Holy Macro! It’s 2,500 Excel VBA Examples.”
!include[Support and feedback]