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

Either of these code lines executes within the Private Sub Workbook_Open no matter whether the MAIN worksheet is the ActiveSheet or not when the workbook opens.

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

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

The problem was trying to define the start and end point of a range with cells that do not belong to the range in a private sub procedure. This may seem like a contradiction since the named ranges have workbook scope but that is the way it played out.

Answer 3

Here's a link to the spreadsheet. It's not elagant but it works.

https://drive.google.com/file/d/1mdUIouozZyuYUS5G8wWqFauXgpLFtzsx/view?usp=sharing

Thanks. Phil.

Answer 4

Hi SweetTasha

Unfortunately, I don't know (or fully understand) your goals.

I have tried to help you in my previous replies, according to the information you have given us

Stepping through your code on the linked file,

I wonder if you kindly consider replacing these lines in the code

*************************************************

SKIP_OPEN:

  Stop

  Dim rw

  Dim insertpoint As Integer

  Dim howmany As Integer

  Dim counter As Integer

  howmany = InputBox("How many transactions?")

  insertpoint = 8

  counter = 0

  rw = insertpoint & ":" & insertpoint

  Debug.Print rw

  Do Until counter = howmany

    Range(rw).Insert Shift:=xlShiftDown

    counter = counter + 1

    rw = insertpoint & ":" & insertpoint

  Loop

************************************************************************

With this one

*************************************************************************************

SKIP_OPEN:

Stop

Dim belowpoint As Integer

Dim abovepoint As Integer

Dim howmany As Integer

howmany = InputBox("How many transactions?")

belowpoint = 7

abovepoint = 8

Range(abovepoint & ":" & belowpoint + howmany).Insert Shift:=xlShiftDown

************************************************************************************************

OR  this one since the variables abovepoint = 8 and belowpoint=7remain constant

**************************************************************************************

SKIP_OPEN:

Stop

Dim howmany As Integer

howmany = InputBox("How many transactions?")

Range("8:" & 7 + howmany).Insert Shift:=xlShiftDown

****************************************************************************************************

They both will do the job.

I hope this helps you

Regards

Jeovany

Answer 5

@Jeovany CV

If you are saying that yours is more elegant--which I suppose I invited when I said mine isn't elegant--then I will agree. However mine also works, although I haven't tested yours. I've spent dozens of hours working on this and no one provided a solution.

You did provide a suggestion on 7/5 that didn't work, and I replied to that to let you know. That was 11 days ago. 

SweetTasha.