code for dynamic range
 

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)

code for dynamic range
 

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)

Code for dynamic range
 

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)

Code for dynamic range
 

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)

Code for dynamic range
 

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

Code for dynamic range
 

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

Code for dynamic range
 

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

Code for dynamic range
 

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

Code for dynamic range
 

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)

Code for dynamic range
 

Hi Barry

Thanks for your reply.

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

---
Per

On 26 Dec., 07:39, Barry Lennox
wrote:
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)

Code for dynamic range
 

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" wrote:

Hi Barry

Thanks for your reply.

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

---
Per

On 26 Dec., 07:39, Barry Lennox
wrote:
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)

Code for dynamic range
 

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

On 27 Dec., 21:02, Barry Lennox
wrote:
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" wrote:
Hi Barry

Thanks for your reply.

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

---
Per

On 26 Dec., 07:39, Barry Lennox
wrote:
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)- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Code for dynamic range
 

Hi Barry

Post the workbook directly to me.

PerJessen69 at Hotmail dot Com

Per

On 30 Dec. 2008, 09:57, Barry Lennox
wrote:
HiPer

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