Range

verluc

Well-known Member
Joined
Mar 1, 2002
Messages
1,451
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
Try this and adjust to your needs.

Sub test()
Dim kopi As Range

Set kopi = ActiveSheet.UsedRange
kopi.Select

End Sub

HTH,
Jay
 
Upvote 0
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":

<pre>
Set kopi = ActiveSheet.UsedRange</pre>
 
Upvote 0
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.
 
Upvote 0
Try,

Sub test()
Dim kopi As Range

Code:
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:"
 
Upvote 0
verluc, this will do it:<pre>

Dim kopi As Range
Dim sAddress As String

sAddress = ActiveSheet.UsedRange.Address
Set kopi = Range("A4:" & Right(sAddress, Len(sAddress) - InStr(1, sAddress, ":")))</pre>

HTH

EDIT:: Sh!t nice code Jay.
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-05-07 16:42
 
Upvote 0
On 2002-05-07 16:41, Jay Petrulis wrote:
Try,

Sub test()
Dim kopi As Range

Code:
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.
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top