use vba range.insert method with excel defined-name as the range?

Question

Range("VendorNameFirst:VendorNameLast").Insert  Shift:=xlShiftDown produces an error. The names in the Range are valid defined-names. Hard-coding the range, for example, range(b3:b4) defeats the purpose of relieving the user do the insert manually, as inserts will be done repetivively. Is there a way to use the defined names?

Thanks. SweetTasha

Answer 1

The code is too bad, IMHO we should replace it completely, we can do it much better.

The general question in here is how to insert lines with VBA in a sheet, so let's shed some light on it.

If we select one row, right-click and choose Insert, Excel insert one row above the current row.

If we select two rows, right-click and choose Insert, Excel insert two rows above the current row.

That sounds a bit silly, but we can determine two rules about the general behavior:

a) We can not insert rows below a row, we can only insert rows above.

b) The size / number of rows determines how many rows are inserted.

And Range.Insert works the same way:

Sub Test()

  Dim HowMany As Long

  HowMany = Application.InputBox("How many transactions?", Type:=1)

  'Be sure we got a valid number

  If HowMany <= 0 Then Exit Sub

  Range("B8").Resize(HowMany).EntireRow.Insert

End Sub

If we run that macro and enter 3 into the input box, we can see this behavior:

Now back to the first question how to use defined names:

It doesn't matter if we say

  Range("B8").Resize(HowMany).EntireRow.Insert

or

  Range("VendorNameLast").Resize(HowMany).EntireRow.Insert

it works the same way.

But what happens if we run this?

Sub Test2()

  Range("VendorNameFirst", "VendorNameLast").EntireRow.Insert

End Sub

Lets look into the sheet:

The code

  Range("VendorNameFirst", "VendorNameLast")

spans a range from B33 till B36, means 4 cells.

According to our rules we expect that 4 rows are inserted above row 33, and that is what happens:

Since the names are created with the Scope workbook, we can also use the code in Workbook_Open without further reference. Only for direct cell references we have to refer to the worksheet:

Sub Workbook_Open()

  Dim HowMany As Long

  HowMany = Application.InputBox("How many transactions?", Type:=1)

  'Be sure we got a valid number

  If HowMany <= 0 Then Exit Sub

  Sheets("Main").Range("B8").Resize(HowMany).EntireRow.Insert

End Sub

Hope that helps.

Andreas.

Answer 2

7/7 I lost my internet connection whille I was writing my previous reply 7/6.

Here are my answers again.

Your error handling is allowing you to skip over code problems

I commented out the OnError statement. It still doesn't show me anything specific about the error.

If you are not willing to do that, open the VBA editor's Immediate window with Ctrl+G. Type or paste in one of these code lines and tap Enter.

?Range("VendorNameFirst:VendorNameLast").Address(0,0)

?Range("VendorNameFirst").Address(0,0)

?Range("VendorNameLast").Address(0,0)

I already had the Immediate window open. It doesn't shed any light. Here's an image of the Immediate window:

You're using the code in a worksheet's private code sheet and not a public module code sheet. Further, VendorNameFirst and/or VendorNameLast are not in the worksheet where you are writing the code. Again, you need to specify the parent worksheet (see code above). In a worksheet's private code sheet, a Range object without a specified parent worksheet always belongs to that worksheet and you cannot define a range on one worksheet using defined names that belong to another.

Do you mean to explicitly specify the parent worksheet on the

Range(rngfirst, rnglast).Insert Shift:=xlShiftDown line?

Thanks. SweetTasha

Answer 3

@Jeeped

*Those debugging methods should cover your problem. If not, report back actual error codes and named range status.*Have you taken a look at my previous reply on July 7?

Answer 4

Have you taken a look at my previous reply on July 7?

Yes, but the only question I found within that last response seemed rhetorical.

Humbly suggest you put a redacted copy of the workbook onto a public share are post a link to the publicly shared workbook back here.

Answer 5

@Jeeped

It was intended to be rhetorical.

Do you mean to explicitly specify the parent worksheet on the

Range(rngfirst, rnglast).Insert Shift:=xlShiftDown line?

Here is the link to the spreadsheet:

https://drive.google.com/file/d/197sKsCaLaqTDb6sVdcbMeaONwW03OBM\_/view?usp=sharing

Thanks. Phil.