Select Last cell in named range VBA

[dimitri]

Hi,
I'm looking for the VBA code that will select the last cell in named range. I can select the first cell by using this code:

Range("NamedRange")(1).select

But i don't know how to select the last cell. What I ultimately want to do is insert 2 rows above the last cell in my named range.

Thanks.

 

[GQue]

Sheet1.Select
Range("MyRange").Select
Selection.End(xlDown).Select
'Insert Entire Row
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'OR
'Move Cells Down
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

 

[Warship]

Sub InsertInNamedRange()
  Dim r As Long, c As Long, rng As Range
  Set rng = Range("NamedRange")
  For r = 1 To 2
    For c = 1 To rng.Columns.Count
      rng.Cells(rng.Rows.Count, c).Insert _
        Shift:=xlDown, _
        CopyOrigin:=xlFormatFromLeftOrAbove
    Next c
  Next r
End Sub

 

[Scott R]

With Range("Rng")
.Cells(.Rows.Count).Resize(2).EntireRow.Insert
End With

 

[dimitri]

Great, thanks Thanks for the options. They work perfectly.

 

[dimitri]

The codes work well, But once the rows have been inserted, I want to select the second to last cell in the range. I thought I could use this code:

Range("NamedRange").Select
Selection.End(xlDown).Select

then Offset to one row up. But the code above only selects the last cell in the range with a value, and I want the absolute last cell in the range, regardless of whether or not it has a value.

 

[Peter_SSs]

The codes work well, But once the rows have been inserted, I want to select the second to last cell in the range. I thought I could use this code:

Range("NamedRange").Select
Selection.End(xlDown).Select

then Offset to one row up. But the code above only selects the last cell in the range with a value, and I want the absolute last cell in the range, regardless of whether or not it has a value.

This adaptation of Scott R's code will do it (I've assumed that the named range is a single column of contiguous cells), but note that selecting things in vba is rarely needed and slows your code.

If you explain what you want to do after selecting the second last cell then a more efficient code can most likely be suggested.

With Range("NamedRange")
    .Cells(.Rows.Count).Resize(2).EntireRow.Insert
    .Cells(.Rows.Count - 1, 1).Select
End With

 

[dimitri]

Peter SSs,
Thanks for your code, it works really well. Once I've selected the second to last cell, I want the active cell to move over 3 columns to the left and enter some text into that cell.

Ex. If the second to last cell in my named range is G5, I need to insert the text "New Tenant" into cell D5.

I've been able to do this by using offset. The code I have below is my adaptation of yours, but if you have a better suggestion I'm certainly open to it.

With Range("FirstRange")
    .Cells(.Rows.Count).Resize(2).EntireRow.Insert
    .Cells(.Rows.Count - 1, 1).Select
    ActiveCell.Offset(0, -3).Select
    ActiveCell.Value = "New Tenant"
End With

Thanks.

 

[Rick Rothstein]

Peter SSs,
Thanks for your code, it works really well. Once I've selected the second to last cell, I want the active cell to move over 3 columns to the left and enter some text into that cell.

Ex. If the second to last cell in my named range is G5, I need to insert the text "New Tenant" into cell D5.

I've been able to do this by using offset. The code I have below is my adaptation of yours, but if you have a better suggestion I'm certainly open to it.

With Range("FirstRange")
    .Cells(.Rows.Count).Resize(2).EntireRow.Insert
    .Cells(.Rows.Count - 1, 1).Select
    ActiveCell.Offset(0, -3).Select
    ActiveCell.Value = "New Tenant"
End With

Thanks.

If that code works, then this modification of it should also work...

With Range("FirstRange")
    .Cells(.Rows.Count).Resize(2).EntireRow.Insert
    .Cells(.Rows.Count - 1, 1).Offset(0, -3).Value = "New Tenant"
End With

Notice all of the "selecting" has been eliminated in favor of chaining the properties together.

 

[dimitri]

Rick,
Wow, that's great. I didn't know I could do it without selecting any cells. Thanks for the help.

Everyone in this thread has been extremely helpful.