VBA offset and resize

[Isabella]

Hi in VBA how do i get the code to copy B and E

.Range("A5").CurrentRegion.Offset(, 1).???

Excel Workbook
	A	B	C	D	E
5	20/03/2011	Test1	12	123.36	James
6	20/03/2011	Test2	12	345.32	James
7	20/03/2011	Test3	12	345.32	James
8	20/03/2011	Test4	12	345.32	James
9	20/03/2011	Test5	12	345.32	James
10	20/03/2011	Test6	12	345.32	James
11	20/03/2011	Test7	12	345.32	James
12	20/03/2011	Test8	12	345.32	James
Sheet5

 

[Richard Schollar]

Hi

You could:

Range("A5").CurrentRegion.Range("B1:B8,E1:E8").Copy

 

[Weaver]

This might work, assumes you don't always know how many rows are in the range. Could depend on what you're doing once copied

Intersect(Range("A5").CurrentRegion, Range("B:B,E:E")).Copy

 

[Isabella]

Richard i have this before .Range
Set rngToCopy =

Also there is not a fixed end range

Hi

You could:

Range("A5").CurrentRegion.Range("B1:B8,E1:E8").Copy

 

[Richard Schollar]

I would go with Weaver's approach:

Set rngToCopy = Application.Intersect(.Range("A5").CurrentRegion,.Range("B:B,E:E"))
 
rngToCopy.Copy
 
'ect etc