VBA: insert a variable number of rows

Question

I've written the code below but I can't get past a line where I want to insert a variable number of rows. The compiler screams that it needs a "list separator or )" where there is a colon. But I can't find other ways of writing this. Please, help! =) So, the problematic line is denoted by two stars from each end. Just above it there is a commented line that I've also tried with no success. Also, would you be so kind and explain me what I need the following commands for (they are also found in the problematic line):Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Finally, any suggestions of how I could improve the code, s.t., experienced programmers don't faint when they read it? =)

Huge thank you in advance,

Option Explicit
Dim ws As Worksheet
Dim Blatt1, Blatt2 As String
Dim Anfangsjahr1, Anfangsjahr2 As Integer
Dim reporting_Jahr1, reporting_Jahr2 As String

Public Sub Dreiecke()

For Each ws In Worksheets

    If ws.Name Like "RVA_H*" Then

            If IsEmpty(Blatt1) = False Then
            Blatt2 = ws.Name
            Anfangsjahr2 = ws.Range("A3").Value
            reporting_Jahr2 = ws.Range("A1").Value
            Else
            Blatt1 = ws.Name
            Anfangsjahr1 = ws.Cells(3, 1).Value
            reporting_Jahr1 = ws.Cells(1, 1).Value
            GoTo X
            End If
    Else: GoTo X
    End If

    If reporting_Jahr1 <> reporting_Jahr2 Then
     MsgBox "Dreiecke von unterschiedlichen Jahren"
     Exit Sub

    ElseIf reporting_Jahr1 = reporting_Jahr2 Then
        If Anfangsjahr1 < Anfangsjahr2 Then

        'Sheets(Blatt2).Rows(3:3+Anfangsjahr2-Anfangsjahr1).EntireRow.Insert
        **Worksheets(Blatt2).Rows(3: 3 + Anfangsjahr2 - Anfangsjahr1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove**

    ElseIf Anfangsjahr1 > Anfangsjahr2 Then
        Worksheets(Blatt1).Rows(3:3+Anfangsjahr1-Anfangsjahr2).Insert Shift:=xlDown

    ElseIf Anfangsjahr1 = Anfangsjahr2 Then GoTo X
    End If


    End If
X: Next ws



End Sub

MatthewD · Accepted Answer · 2016-02-19T14:34:01.607

1

I don't follow exactly what you are trying to get to here but there are some syntax issues.

Not sure if this is what you want, but it fixes the syntax. Do you rally want

Worksheets(Blatt2).Rows("3:" & 3 + Anfangsjahr2 - Anfangsjahr1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

EDIT to expand on OP's question in the comments below
The double quotes are used to enclose a string. So normally when you are referencing rows you can say .Rows("3:9").Insert This is providing the rows you want to work with as a string.
In your case you wanted to dynamically provide last row so we have

our string "3:"
transition from string to variable &
and our variables and math 3 + Anfangsjahr2 - Anfangsjahr1

To make up .Rows("3:" & 3 + Anfangsjahr2 - Anfangsjahr1).Insert

edited Feb 19 '16 at 14:34

answered Feb 19 '16 at 13:58

MatthewD

6,719
5
22
41

Matthew, you are a God! Could you please be as kind and explain me why your code also worked when I removed "Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove". What are those commands intended to do at all? – Lola Feb 19 '16 at 14:23
The sift is the direction the existing rows will be moved in relation to the row you are inserting. Info about CopyOrigin can be found here. http://stackoverflow.com/questions/684531/copyorigin-on-insert-in-excel-vba – MatthewD Feb 19 '16 at 14:25
And, any logical explanation why you put a quotation mark after the colon rather than at the end of the whole expression in brackets, i.e., after 3 + Anfangsjahr2 - Anfangsjahr1? As a blonde I would never put them in the middle as this would imply to me that it will be shown as a text, so why is it not the case here? – Lola Feb 19 '16 at 14:26

score 0 · Answer 2 · answered Feb 19 '16 at 13:58

0

You need to place the row number in double quotation marks and then concatenate the variables to the string using an ampersand:

Worksheets(Blatt2).Rows("3:" & 3 + Anfangsjahr2 - Anfangsjahr1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

answered Feb 19 '16 at 13:58

luke_t

2,935
4
22
38

And, any logical explanation why you put a quotation mark after the colon rather than at the end of the whole expression in brackets, i.e., after 3 + Anfangsjahr2 - Anfangsjahr1? As a blonde I would never put them in the middle as this would imply to me that it will be shown as a text, so why is it not the case here? – Lola Feb 19 '16 at 14:33
1

You are concatenating the string `"3:"` with the number calculated from the sum of `3 + Anfangsjahr2 - Anfangsjahr1`. If that sum were to equal 9, then you're basically creating the string `"3:9"`. By using an ampersand we are concatenating the string and the sum of the variables together. You cannot put variables inside the string, otherwise the variable names will be taken as a string. – luke_t Feb 19 '16 at 14:39

VBA: insert a variable number of rows

2 Answers2