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 .

When you encounter a limitation in Excel, you can often circumvent it by creating a macro. The example is 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)

Next

' 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

Next

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", _ Type:=8) On Error GoTo 0 ' Exit if canceled

If TypeName(PasteRange) <> "Range" Then Exit Sub

' Make sure only the upper-left cell is used Set PasteRange = PasteRange.Range("A1")

' Copy and paste each area For i = 1 To NumAreas

RowOffset = SelAreas(i).Row - TopRow ColOffset = SelAreas(i).Column - LeftCol

SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset) Next i End Sub

Figure 11-11 shows the prompt to select the destination location.

I I

c

n

r

H J

1

1 ■

■1

13

Dl

11

111_37]

n

»

J

Hi]

»

42

ni

H

V

î

B

U

n

*'

H

7 fC-'J

J

Q

31

17

37

n

M i.rn

iUlj

L

41

*

*

11

»

SptfPi

T* -:hw U" tw to i.

■V"

J-

1J

u

r j

M

n

1

ir

bi

TJ

H

»

(

» 1

j

11

1

1J

«I

H

II

12

a

H

H

1

7

»

il

V

1]

S

»

H

H

w

a

n

71

1>

M

a

V

H

rt

w

«

IV

13

• 1

n

11

41

M

a

kl

Ai

id

B

ii

S

15

M

43.

»

73

17

M

M

!

11

12

a

si

K

1J

ii

II»

13

1>

11

U

»

H

13

M

■>

U

17

un

H

IB

lp

1DD

H

J

n

fel

3

3

t>

-u

47

B

B

¿j

13

1

a

4

Ij

71

«

il

m

11

:M

H

M

«

H

V

«

41

31

tr

t

¿J

M

1230

3*2

LUI

UK

«

11«

151

_1

J ■

Figure 11-11: Using Excel's InputBox method to prompt for a cell location. CD-ROM

The companion CD-ROM contains a workbook with this example, plus another version that warns the user if data will be overwritten. The file is named ® copy multiple selection, xlsm .

0 0

Post a comment