Range

[verluc]

I have a macro with the following procedure:
Set kopi = Range("A4:" & Range("K65536").End(xlUp).Address)
What I want to do is indicate the range not only to the laste row,but also to the last column.With other word the hole sheet set to make a copy.
Can somebody help?
Thanks in advance

[Mark O'Brien]

Why not just try the "UsedRange" property?

[verluc]

On 2002-05-07 13:37, Mark O'Brien wrote:
Why not just try the "UsedRange" property?

Marc,can you give me the code to insert please.(How do I insert "UsedRange"?)
Thanks.

[Jay Petrulis]

Try this and adjust to your needs.

Sub test()
Dim kopi As Range

Set kopi = ActiveSheet.UsedRange
kopi.Select

End Sub

HTH,
Jay

[Mark O'Brien]

Is there any data in your first three rows? I'm basing this on the fact that you used A4 as the first cell in your range. Anyway, I've assumed that there's no data in the first three rows, if this is wrong, just repost. Here's how to use "UsedRange":

Set kopi = ActiveSheet.UsedRange

[verluc]

On 2002-05-07 16:30, Jay Petrulis wrote:
Try this and adjust to your needs.

Sub test()
Dim kopi As Range

Set kopi = ActiveSheet.UsedRange
kopi.Select

End Sub

HTH,
Jay

Thanks for answer.But now its make a copy from the used range.I need only used range from row 5,because in the rows 1 to 4 there are my buttons who must not be copied.
Any idea to do this?
Thanks.

[Jay Petrulis]

Try,

Sub test()
Dim kopi As Range

Set kopi = Intersect(ActiveSheet.Rows("5:" & Rows.Count), _
ActiveSheet.UsedRange)

kopi.Select

End Sub

If you want row 4 included, change the "5:" to "4:"

[Mark O'Brien]

verluc, this will do it:

  Dim kopi As Range

  Dim sAddress As String

  

  sAddress = ActiveSheet.UsedRange.Address

  Set kopi = Range("A4:" & Right(sAddress, Len(sAddress) - InStr(1, sAddress, ":")))

HTH

EDIT:: Sh!t nice code Jay.
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-05-07 16:42 ]

[verluc]

On 2002-05-07 16:41, Jay Petrulis wrote:
Try,

Sub test()
Dim kopi As Range

Set kopi = Intersect(ActiveSheet.Rows("5:" & Rows.Count), _
ActiveSheet.UsedRange)

kopi.Select

End Sub

If you want row 4 included, change the "5:" to "4:"

Many thanks,that's great to have such a friends.

[dwhj]

Alternatively :-

Set kopi = Range([A4], Cells.SpecialCells(xlCellTypeLastCell))

Which works whether or not any of the rows 1:3 are blank.


Or, to make sure the last cell has been reset first :-

Activesheet.UsedRange
Set kopi = Range([A4], Cells.SpecialCells(xlCellTypeLastCell))