CODEWITHSUNDEEP
excelvba
user3416285
user3416285

Reputation: 25

How to insert copied range which contains discontinuous rows into other places？

It is a simple question. For example I have rows(1) rows(3), I can union them into same range. I can paste them But I can't insert them !

Upvotes: 0

Views: 90

Answers (2)

Kenneth Chan
Kenneth Chan

Reputation: 530

Hope that I understand your meaning. Excel only can insert continuing copied row. This rule apply on Excel not only VBA.

I would suggest you use selection.count/32768 to get the number of row selected. and then you write some code to insert the number of row and then paste the selection instead of directly insert.

say, if you want to copied rows 1 and 3 to row 5.

you can

set myRng=Range("1:1,3:3") runNum=myRng.count / 32768 for i = 1 to runNum rows(5).insert next myRng.copy rows(5)

Upvotes: 0

user4039065
user4039065

Reputation:

You need to work with the Range.Areas property. The Areas are the contiguous rows that make up the Union.

Sub insertAreas()
    Dim a As Long, rng As Range
    Set rng = Union(Rows(1), Rows(3))
    For a = rng.Areas.Count To 1 Step -1
        rng.Areas(a).Copy
        Rows(10).Insert Shift:=xlDown
    Next a
End Sub

I've reversed the order of the Copy/Insert to maintain the original order.

Upvotes: 1

Related Questions