Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Range

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why not just try the "UsedRange" property?

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this and adjust to your needs.

    Sub test()
    Dim kopi As Range

    Set kopi = ActiveSheet.UsedRange
    kopi.Select

    End Sub

    HTH,
    Jay

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10

    Join Date
    May 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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))

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •