Insert row here ... or not

[BrianDP1977]

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:

Please Login or Register  to view this content.

[Rowan Drummond]

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

[BrianDP1977]

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

[Rowan Drummond]

You're welcome.

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