code for dynamic range

[Barry Lennox]

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)

 

[Jim Cone]

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)

 

[Barry Lennox]

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)

 

[Jim Cone]

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)

 

[Barry Lennox]

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

 

[Per Jessen]

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

 

[Barry Lennox]

Thanks Per
But there is a problem. I get a "compile error syntax error" message with
the following lines in red:
Range("T161").Offset(2, 1).Paste Special Paste:=xlPasteAll,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Where to from here

 

[Jim Cone]

The line continuation character is missing from the end of the first line.
It should read... Paste:=xlPasteAll, _

The second and third lines can (if desired) be combined to read...
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Also, in case you missed it... "Destinatin" should be Destination and
..Offset(0, 0) can be removed - it does nothing.
--
Jim Cone
Portland, Oregon USA



"Barry Lennox"
wrote in message
Thanks Per
But there is a problem. I get a "compile error syntax error" message with
the following lines in red:
Range("T161").Offset(2, 1).Paste Special Paste:=xlPasteAll,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Where to from here

 

[Barry Lennox]

Thanks Guys
This works. Next question how do I put a loop in so that it goes through the
list and stops after the last (in this case first entry). At present I have
to rerun the code but it eventually leaves the remaining entry and then
starts on a table below the original one (in F173)

 

[Per Jessen]

Hi Barry

Thanks for your reply.

What is the list range which you want to loop through. Where do you
want the output?

 

[Barry Lennox]

Hi Per

I have a table, anything form ten to thirty rows and ten to fifty columns, a
list volunteers and the tasks they have done. The information is then
transferred to a letter to each of them listing the dates and tasks. The
letter will be in another worksheet. So I need to go through the the table
one row (a volunteer) at a time,transfer information to the letter (hence the
"transpose"), delete that row and on until all have been done

 

[Per Jessen]

Hi Barry

I'm still not quite sure what you desire, but I hope this can get you
started:

Sub Barry()

FirstRow = Range("FirstCellInList").Row
LastRow = Range("FirstCellInLIst").End(xlDown).Row
For R = LastRow To FirstRow Step -1
'your code to copy and transfer data where R is the row to
manipulate

Next
End Sub

If you want you can mail me a sample workbook.

Regards,
Per

 

[Barry Lennox]

Hi Per

It is probably best if I email the workbook as I am a little bit lost. Di I
post the workbook to this site or to you.

Barry

 

[Per Jessen]

Hi Barry

Post the workbook directly to me.

PerJessen69 at Hotmail dot Com

Per