I am having trouble with "offset" commands in macros.Can someone help me with
code to do the following:
I wish to copy a range two cells to the right of a given cell (after
Selection.End(xlDown).Select) through to the end of the data to the right.
(anything from 1 to 50 columns)

Set rngAtEnd = ActiveCell.End(xlDown).End(xlToRight)
rngAtEnd.Copy
--
Jim Cone
Portland, Oregon USA


"Barry Lennox"
wrote in message
I am having trouble with "offset" commands in macros.
Can someone help me with code to do the following:
I wish to copy a range two cells to the right of a given cell (after
Selection.End(xlDown).Select) through to the end of the data to the right.
(anything from 1 to 50 columns)

Thanks Jim
A help but not quite there. Your code gives me the last cell to the right (a
code that I will be able to use and adapt at another time) But I actually
want to copy the whole range from two cells to the right through to the last
occupied cell to the right)

Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 2)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).Copy
(assumes row data is contiguous)
--
Jim Cone
Portland, Oregon USA



"Barry Lennox"
wrote in message
Thanks Jim
A help but not quite there. Your code gives me the last cell to the right (a
code that I will be able to use and adapt at another time) But I actually
want to copy the whole range from two cells to the right through to the last
occupied cell to the right)

Thanks Jim
It works well. Although I don't yet fully understand it. (I am slowly
learning as I go)
Can you now help me with a loop to clear the whole column (working from the
top up) so that it stops when nothing else is there. In the final operation I
will be transferring data to another sheet and printing between loops.
The code I have is below
Range("GdInvRng").End(xlDown).Select
Selection.Copy
Range("T161").Select
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste
Range("f164").Select
Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 2)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).Copy
Range("T161").Select
ActiveCell.Offset(2, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Range("F164").Select
Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 0)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).ClearContents
Selection.End(xlDown).Select
Range("F163:R163").Select
Application.CutCopyMode = False
Selection.ClearContents

Hi

I'm not sure which columns should be deleted, but I cleaned up your
code a bit, as you don't need to use select (it slow down code).

Range("GdInvRng").End(xlDown).Copy Destinatin:=Range("T161").Offset(2,
0)
Set rngAtStart = Range("f164").End(xlDown).Offset(0, 2)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).Copy
Range("T161").Offset(2, 1).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Set rngAtStart = Range("F164").End(xlDown).Offset(0, 0)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).ClearContents
Range("F163:R163").ClearContents
Application.CutCopyMode = False


Regards,
Per

On 24 Dec., 11:09, Barry Lennox
wrote:
Thanks Jim
It works well. Although I don't yet fully understand it. (I am slowly
learning as I go)
Can you now help me with a loop to clear the whole column (working from the
top up) so that it stops when nothing else is there. In the final operation I
will be transferring data to another sheet and printing between loops.
The code I have is below
Range("GdInvRng").End(xlDown).Select
*Selection.Copy
* Range("T161").Select
* ActiveCell.Offset(2, 0).Select
*ActiveSheet.Paste
*Range("f164").Select
*Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 2)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).Copy
* * Range("T161").Select
* *ActiveCell.Offset(2, 1).Select
* * * Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
* * * * False, Transpose:=True
* * Range("F164").Select
* * Set rngAtStart = ActiveCell.End(xlDown).Offset(0, 0)
Set rngAtEnd = rngAtStart.End(xlToRight)
Range(rngAtStart, rngAtEnd).ClearContents
Selection.End(xlDown).Select
* * Range("F163:R163").Select
* * Application.CutCopyMode = False
* * Selection.ClearContents