Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
Copying a noncontiguous range
If you’ve ever attempted to copy a noncontiguous range selection, you discovered that Excel
doesn’t support such an operation. Attempting to do so brings up an error message: That
command cannot be used on multiple selections .
An exception is when you attempt to copy a multiple selection that consists of entire rows or
columns. Excel does allow that operation.
When you encounter a limitation in Excel, you can often circumvent it by creating a macro. The
example in this section is a VBA procedure that allows you to copy a multiple selection to
another location.
Sub CopyMultipleSelection()
Dim SelAreas() As Range
Dim PasteRange As Range
Dim UpperLeft As Range
Dim NumAreas As Long, i As Long
Dim TopRow As Long, LeftCol As Long
Dim RowOffset As Long, ColOffset As Long
If TypeName(Selection) <> “Range” Then Exit Sub
‘ Store the areas as separate Range objects
NumAreas = Selection.Areas.Count
ReDim SelAreas(1 To NumAreas)
For i = 1 To NumAreas
Set SelAreas(i) = Selection.Areas(i)
‘ Determine the upper-left cell in the multiple selection
TopRow = ActiveSheet.Rows.Count
LeftCol = ActiveSheet.Columns.Count
For i = 1 To NumAreas
If SelAreas(i).Row < TopRow Then TopRow = SelAreas(i).Row
If SelAreas(i).Column < LeftCol Then LeftCol = SelAreas(i).Column
Set UpperLeft = Cells(TopRow, LeftCol)
‘ Get the paste address
On Error Resume Next
Set PasteRange = Application.InputBox _
(Prompt:=”Specify the upper-left cell for the paste range:”, _
Title:=”Copy Multiple Selection”, _
On Error GoTo 0
‘ Exit if canceled
If TypeName(PasteRange) <> “Range” Then Exit Sub
‘ Make sure only the upper-left cell is used
Search JabSto ::

Custom Search