Tom’s Tutorials For Excel: Selecting a Dynamic Range

Tom’s Tutorials For Excel: Selecting a Dynamic Range

Here is a contribution by Tom Urtis to the library of MSDN.

Sometimes you need to locate and manipulate a range of data on the worksheet, but you will not know where the data starts, where it ends, or even if there is any data on the worksheet.

In most cases, you would not need to select the range in order to manipulate it, such as copying or sorting the range. The Select action here is just a demonstration for this example of locating an unknown data range. A message box also advises the user of the data range, as a convenience for cases when the found range is large.

One final note, merged cells are the scourge of Excel programming. An error bypass is in the code in case the only data on the sheet exists in merged cells. A run time error would occur if, curiously, the merged cells are solely horizontal across a row, but not if merged vertically.



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 do not 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 object.

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

This example shows how to select the Used Range range on the current sheet, which includes formatted cells that do not contain data, by using the UsedRange property of the Worksheet object and the Select method of the Range object. Then it displays the address of the range to the user.
Sub SelectUsedRange()
ActiveSheet.UsedRange.Select
MsgBox "The used range address is " & ActiveSheet.UsedRange.Address(0, 0) & ".", 64, "Used range address:"
End Sub

Seemingly most, but not all spreadsheets have their data ranges starting in cell A1, and you’ll need a macro that selects a data range of unknown location or size, regardless of where the data starts and ends. There might be cases when no data exists.

This example shows how to select a data range on the current sheet when you do not 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 do not 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 object.

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