End Shift Down

I’m sick of writing this

Sheet1.Range("B2",Sheet1.Cells(Sheet1.Rows.Count,2).End(xlUp))

I want a new argument to the End property. I want to type

Sheet1.Range("B2").End(xlShiftDown)

I would prefer if this new argument mirrored the code above. That is, I would prefer if it returned a range from the current cell until the last cell in that column. That’s as opposed to returning a range from the current cell to the cell just above the first blank cell, as you should get if you used Ctrl+Shift+Down on the keyboard. But I’d be happy either way.

The other thing this new argument would have to do is know when B2 is the only cell or there is nothing in B2:B?. Oh, screw it. Here’s a table of what I want.

End(xlUp) End(xlDown) End(xlShiftDown)
B2:B10 contains contiguous values B2:B10 B2:B10 B2:B10
B2:B10 contains a blank at B5 B2:B10 B2:B4 B2:B10
B2:B10 has a blank at B2 B2:B10 B2:B3 B2:B10
B2 is the only cell in B B2:B2 B2:B1048576 B2:B2
Column B is empty B1:B2 B2:B1048576 B2:B2

I guess that wasn’t so complicated. It should act just like going up from the last cell except when the column is empty. In that case, it should just return the one cell.

Who’s with me?

12 Comments

  1. Dick Kusleika says:

    Of course I want

    .End(xlShiftToRight)

    also. That just goes without saying.

  2. jon says:

    I just created a RRange function something along the lines of the following:

    RRange(ActiveWorksheet, 1, 1, -1, 5)

    where parameter:
    1 – Worksheet working with
    2 – First row number
    3 – First column number
    4 – Second row number
    5 – Second column number

    A negative number finds the last active row or column number in that column (so -2 would be last in column 2). A 0 would be the very last row in the whole worksheet regardless of the column. If you make the first one negative then the second one > 0 then it will just add to the previous number, e.g., 1st row = -1 then 2nd row = 2 would give you the last filled row plus 2 more rows (3 rows total). Makes it a breeze working with ranges!

  3. rgor says:

    I took an almost identical approach – with a few additional checks could be adjusted to respond to your requirements table.

    Public Function LoadRangeAsVariant(ByRef WBname As String, _
    ByRef WSname As String, _
    ByRef Row1 As Long, _
    ByRef RowN As Long, _
    ByRef Col1 As Long, _
    ByRef ColN As Long, _
    Optional ByRef ConvertTo1Dvariant As Boolean = True) _
    As Variant
    ‘Loads range from spreadsheet and returns it as a variant
    ‘- WBname: Workbook name
    ‘- WSname: Worksheet name
    ‘- Row1: first row
    ‘- RowN: last row; if NEGATIVE, it’s calculated as the last Row in column -RowN
    ‘- Col1: first column
    ‘- ColN: last column; if NEGATIVE it’s calculated as the last Col in row -ColN
    ‘- ConvertTo1Dvariant: if the range is 1D, converts it into a 1D variant

    This uses the LastRow and LastColumn functions found in Ron’s web (http://www.rondebruin.nl/last.htm), with minimum changes:

    Public Function LastRow(ByRef WS As Worksheet, Optional ColIndex As Long = 1) As Long
    LastRow = 0
    On Error Resume Next
    LastRow = WS.Cells(WS.Rows.count, ColIndex).End(xlUp).row
    On Error GoTo 0
    End Function

    Public Function LastCol(ByRef WS As Worksheet, Optional RowIndex As Long = 1) As Long
    LastCol = 0
    On Error Resume Next
    LastCol = WS.Cells(RowIndex, WS.Columns.count).End(xlToLeft).Column
    On Error GoTo 0
    End Function

  4. snb says:

    I’d like, similar to Word’s VBA : activedocument.paragraphs.last

    columns(1).cells.last
    columns(4).cells.first
    columns(7).areas(1).cells.first
    columns(9).areas(2).cells.last

    rows(1).cells.last
    rows(4).cells.first
    rows(7).areas(1).cells.first
    rows(9).areas(2).cells.last
  5. jon says:

    @rgor,

    That’s awesome. Just shows how much sense that method makes! Makes it really simple.

  6. Benzadeus says:

    I don’t know if snb is talking about the example below, but that really annoys me:

    Sub fExample()
        Dim rngFirst As Range
        Dim rngLast As Range
       
        Set rngFirst = Sheets("Sheet1").Range("C5:G9")(1)
        Set rngLast = Sheets("Sheet1").Range("C5:G9")(Sheets("Sheet1").Range("C5:G9").Cells.Count)

        MsgBox rngFirst.Row ' equals 5
        MsgBox rngLast.Row  ' equals 9
    End Sub
  7. Jeff Weir says:

    You’d think this would be easy if using tables. But because the ListColumns doesn’t have a LastRow property, I can only come up with monsters like this:

    Range("B"&Right(Range("b2").ListObject.Range.Address,instr(1,strreverse(Range("B2").ListObject.ListColumns(1).Range.address),"$"))).Select
    Range("B2").ListObject.ListColumns(1).Range.Cells(Range("B2").ListObject.ListRows.Count + 1, Range("B2").Column - Range("B2").ListObject.Range.Column + 1).Select
    Intersect([B2].ListObject.Range, [B2].EntireColumn).Cells([B2].ListObject.listrows.count + 1).select

    …or if you know that B2 happens to be in a column called ‘SomeColumn’ in Table 1:

    Range("Table1[SomeColumn]").Cells(Range("Table1[SomeColumn]").ListObject.ListRows.Count).Select

    So count me in. And lets get a LastRow property for listobjects while we’re at it.

  8. jkpieterse says:

    Jeff:

    Public Function LastRow(oLo As ListObject) As Range
        Set LastRow = oLo.ListRows(oLo.ListRows.Count).Range
    End Function

    Sub foo()
        LastRow(ActiveCell.ListObject).Select
    End Sub
  9. Jeff Weir says:

    That’s fine if your table has only one column. But what if it doesn’t?

  10. Jeff Weir says:

    Sorry, realised my comment above is not clear. What I mean is this: what if you want to select the bottom cell in the current table row? The shortest way I can work out to do this is:
    Selection.ListObject.Range.Cells(selection.ListObject.ListRows.Count + 1, selection.Column – selection.ListObject.Range.Column + 1).Select

    Also, I wish there was an easy way to find out the name or index number of the listrow column that a particular cell is in.
    This is the shortest I can find
    ? Intersect(Selection.ListObject.HeaderRowRange, Selection.EntireColumn)

    I would have thought that these type of things would be useful enough that MS would have them as standard listobject properties.

  11. With my little function It would be:

    Intersect(Activecell.entirecolumn, LastRow(ActiveCell.ListObject)).Select

    I agree finding your way through listobjects can be cumbersome. But so can addressing certain ranges be.

  12. MSimms says:

    re: “I would have thought that these type of things would be useful enough that MS would have them as standard listobject properties.”
    Excellent “thought and idea for improvement”.

    MSFT has neither these days….

    Microsloth, Inc.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: