[Solved] Insert here ... or not

[BrianDP1977]

I don't understand why this won't work. This code is a slightly adjusted code offered by HalfAce in a different post:

Sub Insert_Row(nRange As Range) 
Dim InsrtRw As Long

InsrtRw = Range(nRange).Cells(Rows.Count, 1).End(xlUp).Row 
Range(nRange).Rows(InsrtRw).EntireRow.Copy 
Range(nRange).Rows(InsrtRw).EntireRow.Insert 
Application.CutCopyMode = False 
End Sub

What I'm trying to do is create an Insert_Row code that will be passed a named range of cells (through the nRange variable). Within this selected range of cells, I want a whole row added to the last line within the specified range of cells. I need this added row to become part of the named range (expressing the range of cells as a dynamic name should accomlish this) and also maintain the equations contained in the cells (thus the reason for the copy call). Thanks for the help.

 

[BrianDP1977]

That seems to work. Thanks for not giving up. I would still kind of like anything not containing an equation to clear from the row (to empty cells for entry) but this is not a major issue. Now that I know what that one section was doing in the other equation, I'll see if I can't tag that on somehow. Thanks again very much.

 

[BrianDP1977]

I hate to throw one more curve ball at you but is there any way to keep this insert from screwing around with current border settings? Right now, the last row in the section has a thin top border and the bottom border is a thick line. When it does the insert, the inserted row has a thick border on both the top and the bottom. I'm sure I can find a workaround but just curious if there is a simple solution. Thanks.

 

[Barrie Davidson]

Batter up!

How about changing the code to:

Sub Insert_Row(nRange As Range)

nRange.Cells(nRange.Rows.Count, 1).EntireRow.Copy
nRange.Cells(nRange.Rows.Count, 1).EntireRow.Insert
Application.CutCopyMode = False
With nRange.Offset(nRange.Rows.Count - 1).Resize(1).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With

End Sub

 

[BrianDP1977]

I'm glad your keeping your sense of humor with me. Anyway, it worked for the first cell in the row but not all the rest. I'd say "we are so close" but you're doing all the work. Thank you again for the help.

 

[BrianDP1977]

Disregard last. It had to do with how I was defining the range. It works great now. Thank you very much.

 

[Barrie Davidson]

"Never give up...Never surrender" Trivia - what movie is that from (gotta make this fun)?

Now, on to adjusted code

Sub Insert_Row(nRange As Range)

nRange.Cells(nRange.Rows.Count, 1).EntireRow.Copy
nRange.Cells(nRange.Rows.Count, 1).EntireRow.Insert
Application.CutCopyMode = False
With nRange.Offset(nRange.Rows.Count - 1).EntireRow. _
    SpecialCells(xlCellTypeConstants).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With

End Sub

How's that?

 

[Barrie Davidson]

Disregard last. It had to do with how I was defining the range. It works great now. Thank you very much.

Too late, I already posted a suggested solution. Can you try it and let me know if it works?

 

[BrianDP1977]

Actually, the other code works better. For some reason, this one formats some borders bu not others. I'm assuming it's because not all cells in the database have inputs. Sometimes they get left out. The otehr one works well though. Thank you very much.

 

[BrianDP1977]

If you are still interested in tinking with the one set of code you gave me, I could still use a way to clear cells of their values if they do not contain equations (but leave the equation cells with their equations) thus allowing empty cells for entry. It's no biggie and I am very happy with what I have now. It works great Thank you again.

Here's the code I'm using:

Private Sub CommandButton1_Click()
  Dim Range_Names As Range
  
  Set Range_Names = Range("Ext_DP6_P1")
  Call Insert_Row(Range_Names)
End Sub

Sub Insert_Row(nRange As Range)

nRange.Cells(nRange.Rows.Count, 1).EntireRow.Copy
nRange.Cells(nRange.Rows.Count, 1).EntireRow.Insert
Application.CutCopyMode = False
With nRange.Offset(nRange.Rows.Count - 1).Resize(1).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With

End Sub

 

[Barrie Davidson]

How about this then?

Sub Insert_Row(nRange As Range)

nRange.Cells(nRange.Rows.Count, 1).EntireRow.Copy
nRange.Cells(nRange.Rows.Count, 1).EntireRow.Insert
Application.CutCopyMode = False
With nRange.Offset(nRange.Rows.Count - 1).EntireRow.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
nRange.Offset(nRange.Rows.Count - 1).Resize(1). _
    SpecialCells(xlCellTypeConstants).ClearContents

End Sub