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

@Andreas Killer

*The code is too bad, IMHO we should replace it completely, we can do it much better.*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

WOW. It is much better. Compact. Commented. Error checking. This is a Master Class.

Thanks. SweetTasha.