Select & Copy Non-Contiguous Cells

Hi Friends of Excel Forum,

I'm trying to copy Non-Contiguous Cells which may be 1 row or more. I'm not good in writing VBA codes, so I recorded the following:

Sub Macro1()

    ActiveCell.Range("A1:D1,G1").Copy
    
End Sub

This seemed to work for 1 row only. What if I select A1:A10 and I need it to copy A1:D10 together with G1:G10?

Thanks.

Originally Posted by Ichigo

What if I select A1:A10 and I need it to copy A1:D10 together with G1:G10?

Sub Test()
  Range(Selection.Address & "," & Selection.Offset(, 6).Address).Copy
End Sub

You are welcome.
I don't quite catch what you want, but I try to explain with this example :

Suppose the current activecell is on A1, and you want to select A1:A3 and E1:F3

Sub Test2()
  Range(Selection.Resize(3).Address & "," & Selection.Offset(, 4).Resize(3, 2).Address).Copy
End Sub

As as start, the Selection refer to A1, so to refer A1:A3, we must enlarge by 3 rows --> Selection.Resize(3).Address

The second range (E1:F3), the top left cell of this second range is E1, we locate this cell from Selection by offsetting 4 columns,
so E1 = Selection.Offset(, 4)
Now to enlarge E1 to E1:F3, we use .resize method --> Selection.Offset(, 4).Resize(3, 2).Address (enlarge by 3 rows and 2 columns)

Hi Ichigo,
I am also not too clear on exactly what you want.

But it may be worth pointing out that you can only copy a Range Object made up of of Non-Contiguous Cells ( that is to say a Range Object made up of two or more Areas of Contiguous Cells), If the Areas of the Range Object are “In Line”

I mean you could copy these two Areas in a Worksheet ( The resulting Range Object has a Range.Areas(1) and Range.Areas(2) )
Using Excel 2007 32 bit

Areas1,Cells(1)	Areas1,Cells(2)
.
	.
Areas2,Cells(1)	Areas2,Cells(2)

Worksheet: RangeObjects

And you can copy these three Areas in a worksheet ( The resulting Range Object has a Range.Areas(1) , Range.Areas(2) and Range.Areas(3) )
Using Excel 2007 32 bit

rngObj	rngObj	rngObj			rngObj	rngObj		rngObj
rngObj	rngObj	rngObj			rngObj	rngObj		rngObj

Worksheet: RangeObjects
_.....

But you cannot copy these two Areas of cells ( It is never the less a valid Range Object obtained with via like RngObject=Range("A3:A4,D1:E2") or RngObject=Application,Union( Range("A3:A4" ,Range("D1:E2") . The resulting Range Object has a Range.Areas(1) and Range.Areas(2)
http://powerspreadsheets.com/excel-vba-range-object/ )

Using Excel 2007 32 bit

			rngObj	rngObj
			rngObj	rngObj
rngObj
rngObj

Worksheet: RangeObjects

Alan

@Alan :
A nice explanation

@Ichigo :
You are welcome.

Try this :

Sub Test3()
  Range(Selection.Address & "," & Selection.Offset(, 4).Resize(, 2).Address).Copy
End Sub

Hi Ichigo
Thanks for the Rep

Here how to mark Thread as solved: ( You need to be logged in to do that )
ThreadSolved.JPG
http://imgur.com/upfFsAT
Attachment 491107

Originally Posted by Ichigo

as to put them into words were not easy compared to face-to-face.

I agree. Internet is not so good as Real Life contact, but we have the chance to meet people easilly from very far away
Alan

You are welcome Ichigo, many thanks for the rep.points.
Don't forget to mark this thread as solved :
please select Thread Tools from the menu link above and mark this thread as SOLVED.

Regards