Designating a Range Using End(xlToRight)

Topic

New Reply

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”).Calculate

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

Reply | Quote

Replies

Stephen, I am not too clear on what you want but wonder if

 Selection.CurrentRegion.Select 

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

Andrew C

Reply | Quote

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

Reply | Quote

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

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

As example if your data runs from Y9 to AH then

	LastDataCol(Range("Y9", Range("Y9").End(xlToRight)),1)

should return 10, while

	LastDataCol(Range("Y9", Range("Y9").End(xlToRight)))

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

Range("Y9").Select
Selection.Offset(0, LastDataCol(Range("Y9", Range("Y9").End(xlToRight)), 1) - 1).Select

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

Reply | Quote