Insert row here ... or not
 

I stumbled across this code on another site. It functions like I nee
it to (i.e. inserts a row after the last row of data along with an
equations in the row while also deleting anything that is not a
equation) except that in it's current state it defines the last ro
where the row is to be inserted by locating the last row on the shee
containing data and inserting it there. Instead, I want to be able t
restrict the code to a specific range on the sheet where it is to plac
the new row (i.e. my sheet contains various sections of data ... I wan
to define the rows that make up one of these sections (let's say A4:A
named as D_Names) and have the code insert a row at the end of thi
section instead of at the bottom of the entire sheet). Here's th
code:


Code
-------------------
Private Sub CommandButton1_Click()
Dim LastRow As Range

Set LastRow = [A65536].End(xlUp).EntireRow
With LastRow
.Offset(1, 0).Insert
.Copy .Offset(1, 0)
On Error Resume Next
.Offset(1, 0).SpecialCells(xlCellTypeConstants, 23).ClearContents
On Error GoTo 0
End With
End Su
-------------------

--
BrianDP197
-----------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...fo&userid=2911
View this thread: http://www.excelforum.com/showthread.php?threadid=49005

Insert row here ... or not
 

Maybe:

Sub test()
Dim nm As Name
Dim iRow As Long
Set nm = ThisWorkbook.Names("D_Names")
iRow = Mid(nm.RefersTo, InStrRev(nm.RefersTo, "$"), 255)
With Rows(iRow)
.Offset(1, 0).Insert
.Copy .Offset(1, 0)
On Error Resume Next
.Offset(1, 0).SpecialCells(xlCellTypeConstants, 23). _
ClearContents
On Error GoTo 0
End With

End Sub

Hope this helps
Rowan

BrianDP1977 wrote:
I stumbled across this code on another site. It functions like I need
it to (i.e. inserts a row after the last row of data along with any
equations in the row while also deleting anything that is not an
equation) except that in it's current state it defines the last row
where the row is to be inserted by locating the last row on the sheet
containing data and inserting it there. Instead, I want to be able to
restrict the code to a specific range on the sheet where it is to place
the new row (i.e. my sheet contains various sections of data ... I want
to define the rows that make up one of these sections (let's say A4:A6
named as D_Names) and have the code insert a row at the end of this
section instead of at the bottom of the entire sheet). Here's the
code:


Code:
--------------------
Private Sub CommandButton1_Click()
Dim LastRow As Range

Set LastRow = [A65536].End(xlUp).EntireRow
With LastRow
.Offset(1, 0).Insert
.Copy .Offset(1, 0)
On Error Resume Next
.Offset(1, 0).SpecialCells(xlCellTypeConstants, 23).ClearContents
On Error GoTo 0
End With
End Sub
--------------------

Insert row here ... or not
 

It seems to work well. Thank you very much for the help.


--
BrianDP1977
------------------------------------------------------------------------
BrianDP1977's Profile: http://www.excelforum.com/member.php...o&userid=29110
View this thread: http://www.excelforum.com/showthread...hreadid=490050

Insert row here ... or not
 

You're welcome.

BrianDP1977 wrote:
It seems to work well. Thank you very much for the help.