Add Row at End of Named Range

[smac001]

Please help. I am trying to add a row to the last row in a named range, I have found the following code:

Sub newRow()
Dim target As Range
Dim cell As Range
Dim rowNr As Integer
Set target = Range("FDrivers")
If line <> -1 Then
rowNr = line
Else
rowNr = target.Rows.Count
End If
target.Rows(rowNr + 1).EntireRow.Offset(1, 0).Insert
target.Rows(rowNr).Copy target.Rows(rowNr + 1)
For Each cell In target.Rows(rowNr + 1).Cells
If Left(cell.Formula, 1) <> "=" Then cell.Clear
Next cell
End Sub

This does work but it adds the row to the first row in the range and not the last. How do I add to the last row in the range?

 

[JoeMo]

Assuming the range name is FDrivers:

Sub testit()
Dim R As Range, rws As Long
Set R = Range("FDrivers")
rws = R.Rows.Count
With R
   Set R = R.Resize(rws + 1)
   R.Name = "FDrivers"
End With
End Sub

 

[smac001]

Hi Joe,

The range is growing when I run the code, but the row is not being added within the range in the worksheet.

 

[JoeMo]

Hi Joe,

The range is growing when I run the code, but the row is not being added within the range in the worksheet.

I don't understand. The named range row count should increase by one with the addition of one row at the end of the range. Is that not happening?

 

[smac001]

Hi,

I dont if this makes a difference but I also tried using this code, it worked as it added the first insert to the last range, however once the button was clicked again the row was inserted into another range. And yes you are correct in assuming that FDrivers is the named range.


With Range("FDrivers")
.Rows(.Rows.Count + 1).EntireRow.Offset(-1, 0).Insert
.Resize(.Rows.Count + 1, .Columns.Count).Name = .Name.Name
End With

 

[smac001]

The named range row count is increasing the actual row is not being added within the range on the spreadsheet.

 

[JoeMo]

The named range row count is increasing the actual row is not being added within the range on the spreadsheet.

Sorry, I'm not comprehending. If the named range row count increases by one row, then the macro is doing what I thought you wanted it to do. If a row is not being added, then how is the named range row count increasing?

 

[smac001]

To explain a little more on what I am trying to do I have 4 named ranges, the first range FDrivers before row is added is A30:N44, the header for the second range SDriver is A45:N46 and actually range is A47:N61. When the code is ran you sent back visually the row is not added but when you check the Name Manager the range has increased. If I use the second code that I sent you the row is visually added, the range increases, but if i add a second row the row is added into the header for SDrivers (second named range) instead of finding the last row again in the first named ranged (FDrivers).

 

[JoeMo]

Just add whatever number of rows you want above the named range sDriver, then resize FDrivers to include those rows.

 

[smac001]

I need to be able to do this with vba the workbook will be used by multiple people.