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

To describe an inclusive range you can separate the start and end with a comma. Have you tried,

Range("VendorNameFirst", "VendorNameLast").Insert  Shift:=xlShiftDown

Answer 3

@Jeeped

I corrected that and it gives the same result.

Seems like it might help if the error gave an err.number.

Thanks for your help. SweetTasha.

Answer 4

@SweetTasha

Please, replace

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

Shift:=xlShiftDown

with

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

On the sample .gif below the cells in yellow are named ranges VendorNameFirst" and "VendorNameLast

respectively

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany