12 thoughts on “End Shift Down”

1. Of course I want

   .End(xlShiftToRight)

   also. That just goes without saying.

2. 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. 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. 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. @rgor,

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

6. 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. 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. 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. That’s fine if your table has only one column. But what if it doesn’t?

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