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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Use this line:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

**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:

Please Login or Register  to view this content.

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