Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Designating a Range Using End(xlToRight)

    In the code, below, I clear an area and then fill down with data from the row above the area that I cleared.
    Dim LastRow As Long
    LastRow = Application.CountA(ActiveSheet.Range("W:W")) + 4
    Range("P9:P" & LastRow).ClearContents
    Range("P8:P8").Select
    Selection.AutoFill Destination:=Range("P8:P" & LastRow)
    Worksheets("projstart").UsedRange.Columns("P:P").C alculate

    I want to continue by applying the above code to a range using "End(xlToRight)" and "End(xlDown)". I have gotten as far as the next 2 statements but do not know how to extend the range designation to the bottom of the block of data:

    Range("Y9:Y9").Select
    'This extends the selection from cell B4 to the last cell in row four that contains data.
    Range("Y9", Range("Y9").End(xlToRight)).Select
    End(xlToRight)

    Could someone please help.
    Thanks
    Stephen

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Designating a Range Using End(xlToRight)

    Stephen, I am not too clear on what you want but wonder if <pre> Selection.CurrentRegion.Select </pre>

    would be of help. It extends the current selection to include all contiguous cells in the block of data.

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Designating a Range Using End(xlToRight)

    Andrew:
    This may be what I need but how do I use it?
    With re: to the code in my POST, I would like to replace the "P" in the two statements:
    1) Range("P9:P" & LastRow).ClearContents and
    2) Selection.AutoFill Destination:=Range("P8:P" & LastRow)
    with the last column of my data to the right.
    I think that the following will select that column for me:
    Range("Y9", Range("Y9").End(xlToRight)).Select
    End(xlToRight)
    but I do not know how to pass that to the above two statement to replace the "P".
    I hope that this clarifies what I need.
    Thanks <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Designating a Range Using End(xlToRight)

    Stephen,

    I am including here a user defined function which might help. It returns the offset to the last column in a range, either relative to the the first cell (Y9 in your case) in the selection or relative to column A <pre>Function LastDataCol(rng As Range, Optional UseOffSet As Boolean) As Integer
    Dim CellCount As Integer, ColumnOffset As Integer, i As Integer
    Application.Volatile
    CellCount = rng.Count
    If UseOffSet Then
    ColoumnOffset = rng.Column - 1
    End If
    For i = CellCount To 1 Step -1
    If Not IsEmpty(rng(i)) Then
    LastDataCol = rng(i).Column - ColoumnOffset
    Exit Function
    End If
    Next i
    End Function</pre>


    As example if your data runs from Y9 to AH then <pre> LastDataCol(Range("Y9", Range("Y9").End(xlToRight)),1)</pre>

    should return 10, while <pre> LastDataCol(Range("Y9", Range("Y9").End(xlToRight)))</pre>

    should return 34. I suspect the first value is most useful as you can use it as an offset from Y9.

    The following should select the last column in row 9<pre>Range("Y9").Select
    Selection.Offset(0, LastDataCol(Range("Y9", Range("Y9").End(xlToRight)), 1) - 1).Select</pre>

    I am not sure if that is the column you are trying to access or all columns between Y9 and whatever the last column is.

    I hope that can assist you in some way.

    Andrew

Posting Permissions

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