End Shift Down

I’m sick of writing this


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


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 thoughts on “End Shift Down

  1. 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!

  2. 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

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



  4. 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

  5. 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("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:


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

  6. Jeff:

    Public Function LastRow(oLo As ListObject) As Range
        Set LastRow = oLo.ListRows(oLo.ListRows.Count).Range
    End Function
    Sub foo()
    End Sub
  7. 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.

  8. 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.

  9. 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? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.