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

@Jeovany CV

Removing the continuation didn't stop the error. 

SweetTasha.

Answer 3

The phrases 'produces an error' and 'didn't stop the error' are neither valid error codes nor adequate descriptions of your problem. Please elaborate with any error codes/descriptions that are occurring as well as a true description of the behaviour that is not what you expected.

Answer 4

You asked:The phrases*'produces an error' and 'didn't stop the error'*are neither valid error codes nor adequate descriptions of your problem.

Here's what I said:

7/5 ___________________________________________________________________--

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?

7/6 Jeovany CV

suggested trying it without the continuation, the whole statement on one line.

7/6

@Jeovany CV

Removing the continuation didn't stop the error. 

SweetTasha.

7/6 You said

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

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

I didn't respond to that, because I thought people looking at/following the thread would look at the entire conversation, not just their own posts.

To address your latest post:

The process doesn't produce an error message nor an error code (err.number). It merely goes to the OnError routine.

Will you let me know what additional clarification I can provide?

SweetTasha.

Answer 5

Your error handling is allowing you to skip over code problems. You might want to disable it temporarily by placing a ' at the beginning to comment it; e.g. 'On Error GoTo xxxxx. That will show you what error is being produced.

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)

You are almost assuredly going to receive the following error. This is the error code and description I mentioned earlier.

There are a limited number of reasons for this error. Following are the three most probable.

• You've simply mistyped VendorNameFirst and/or VendorNameLast. Double-check Formulas, Defined Names, Name Manager to make sure they are what you expect them to be.
• You defined the two named ranges with worksheet scope but are using them when another worksheet is active. Again, this can be checked in the Name Manager. If this is the case, you need to define the proper parent worksheet of the Range object. For example:

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

In a public module code sheet, a Range object without a specified parent worksheet always belongs to the ActiveSheet and you cannot define a range on the active worksheet using defined names that belong to another.

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

Those debugging methods should cover your problem. If not, report back actual error codes and named range status.