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

 

[Barrie Davidson]

I think it's because of the variable InsrtRw - which I don't think you need. Try this and let me know the results:

Sub Insert_Row(nRange As Range) 

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

Regards,

 

[BrianDP1977]

Still no luck. Here's how I'm calling Insert_Row from the sheet:

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

D_Names is now the dynamic named range. Here's Insert_Row as displayed on a module:

Sub Insert_Row(nRange As Range)

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

 

[Barrie Davidson]

Still no luck.

So what's happening with the code? Is it bombing at a line or is it inserting at the wrong place?

 

[BrianDP1977]

It looks like it gets to this line:

Range(nRange).Cells(Rows.Count, 1).EntireRow.Copy

and gives me this error:

Run-time error '1004':
Method 'Range' of object '_Global' failed

 

[BrianDP1977]

Here's the original source code which worked fine except that I can't define a range:

Sub Demo2() 
Dim InsrtRw As Long 
InsrtRw = Cells(Rows.Count, 1).End(xlUp).Row 
Rows(InsrtRw).EntireRow.Copy 
Rows(InsrtRw).EntireRow.Insert 
Application.CutCopyMode = False 
End Sub

Don't know if this will help.

 

[BrianDP1977]

After some more digging I came accross this 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

This actually works more in accordance with what I need but, again, I am still unable to figure out how to define a specific range on the sheet. Instead, similar to others I've found, this code searches the entire sheet and counts the number of rows with data in them and then uses that as the last row for the insert. How do I restrict the code to a specific range on the sheet? If I knew what "[A65536]" above was I may have a chance at figuring this out on my own but I'm at a loss.

 

[Barrie Davidson]

It looks like it gets to this line:

Range(nRange).Cells(Rows.Count, 1).EntireRow.Copy

and gives me this error:

Run-time error '1004':
Method 'Range' of object '_Global' failed

Ooops, change that to

Sub Insert_Row(nRange As Range) 

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

Does that work for you?

 

[BrianDP1977]

Sorry Barrie, but that still doesn't seem to work. I'm not sure what's going on. Therefore, I'm now working with the changed code above which seems to work a little better. However, now I've run into a new set of problems and am about to call it a night. Here's what I have:

Private Sub CommandButton1_Click()
  Dim LastRow As Range

  Set LastRow = Range("D_Names").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

This code now references my named range (D_Names) but when it goes to do the insert, it begins the offset portion by starting at the top of the page instead of at the top of the range and counting down the number of rows determined by the "set LastRow" portion. I think this is a simple solution but my brain is pretty fried. Thanks for the help.

Also, what exactly is the "xlCellTypeConstants, 23" portion doing?

 

[Barrie Davidson]

Well, I'm not giving up!! How about:

Private Sub CommandButton1_Click()
  Dim Range_Names As Range
  
  Set Range_Names = Range("D_Names")
  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
End Sub

I tested this (didn't test on my previous posts) and seems to do what you want.

That line containing "xlCellTypeConstants,23" is clearing all cells containing constants - either errors, logical constants, numbers, and text values (and I don't think you need it).

Anyway, let me know if this new code does what you need.