Range for activecell.offset

**Folshot** · 05-09-2011, 06:04 PM

I normally use the following statement to copy the second cell located to the right of the found cell back into the original worksheet:

AtiveCell.Offset(0, 2).Select
ActiveCell.Copy

Worksheets("Tab1").Select
ActiveSheet.Cells(l, 7).Activate
ActiveCell.PasteSpecial

The above works fine. However I would like to use a range instead, i.e.I would like to copy the second, third and fourth cell located to the right of the found cell. I used the following:

Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Select
ActiveCell.Copy

Worksheets("Tab1").Select
ActiveSheet.Cells(l, 7).Activate
ActiveCell.PasteSpecial

However the above does exactly the same as the first macro? Any ideas why?

PS: what should I use to copy and paste the 2nd, 3rd and 5th cells to the right in one go?

Thanks,
Antoine

**tigeravatar** · 05-09-2011, 06:22 PM

Antoine,

Instead of these two lines:

Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Select
ActiveCell.Copy

Use this line:

Range(ActiveCell.Offset(0, 2).Address & ":" & ActiveCell.Offset(0, 4).Address).Copy

Hope that helps,
~tigeravatar

**Folshot** · 05-09-2011, 06:54 PM

Thanks a lot tigeravatar, it works great.

Would you know how to combine a range + a single cell? For example, I would like to copy the 2nd, 3rd and 5th cell located to the right of the found cell.

Thanks again,
Antoine

**Colin Legg** · 05-09-2011, 07:00 PM

You can use the Application Union method to build up a union of ranges, eg:

Union(ActiveCell.Offset(, 2), ActiveCell.Offset(, 3), ActiveCell.Offset(, 5)).Copy

**Folshot** · 05-09-2011, 07:13 PM

Hi Colin,

It does help as well, thanks. However my final spreadsheet has around 70 columns, hence I was wondering if it was possible to combine ranges and single columns (i.e. column 12 to 34 with columns 57 and 68) rather than having to enter each column individually.

Antoine

**tigeravatar** · 05-09-2011, 08:25 PM

Antoine,

You can do that with the following:

Range(ActiveCell.Offset(0, 2).Address & ":" & ActiveCell.Offset(0, 3).Address & "," & _
      ActiveCell.Offset(0, 5).Address & "," & _
      ActiveCell.Offset(0, 12).Address & ":" & ActiveCell.Offset(0, 34).Address & "," & _
      ActiveCell.Offset(0, 57).Address & ":" & ActiveCell.Offset(0, 68).Address) _
      .Copy

Notes:
-Use ":" to combine two cells into a solid range
-Use "," to add a new (separate) group to the range
-The way I have it formatted is optional. It makes it easier for me to see which groups are in the range quickly if each group is on its own line. You'll notice the offset(0,5) doesn't have a combiner ":" because that cell is by itself.

Hope this helps,
~tigeravatar