Need a Macro To Add Rows in Excel

[floridagunner]

I need a unique Macro for Excel and I cant seem to find the solution in VBA Help.

Basically what I need to do is reference a cell and automatically insert a number of rows according to the number in the cell.

EG

Column R

5
1
2
6
8
4
3

This means if cell R1 contains the number (5) I need a macro that will insert 4 rows below that cell. If R2 contains the number 2, the macro should insert 1 row below that cell.

Any help is greatly appreciated.

Thanks

 

[Andrzejek]

This may get you started:

If you have a value 5 in cell R1, this will insert 5 rows below R1

Dim intRows As Integer

Range("R2").Select
For intRows = 1 To Range("R1").Value
    Selection.EntireRow.Insert
Next intRows

Have fun.

---- Andy

 

[floridagunner]

Hi Andy thanks for your response. I tried this code but I keep getting back the message "Invalid Outside Procedure"
with ("R2") highlighted.

Thanks

 

[SkipVought]

floridagunner,

It is general practice to display a code simppet as Andy has so graciously done for you.

It is up to YOU, to cast that in whatever way suits your need. Code must run within a PROCEDURE; either a Sub or a Function. For instance, someone might post a code snippet like...

  MsgBox "Hello, world!"

You could then include that in a procedure like...

Sub DisplayMessage()
  MsgBox "Hello, world!"
End Sub

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[floridagunner]

Hello Skip, I am sorry for having neglected to display a code simppet. I am new to both VBA and this site. I will be carefull in my future posts.
I still cant get around what the flaw is in Andy's code

Thanks

 

[SkipVought]

Your error, "Invalid Outside Procedure"

needs,

Code must run within a PROCEDURE; either a Sub or a Function

per my example.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[floridagunner]

Ok thanks Skip I will try this

 

[bdmangum]

floridagunner,

Try the code below. Make sure to swap out the Sheets(1) with Sheets("YourSheetName") or Sheets(YourSheetNumber).

Best of luck!

Sub Insert_rows()

Dim intRows As Integer
Dim count As Integer

count = 1   'Initialize variable to starting row number
While Sheets(1).Cells(count, 18).Value <> ""    'Loops until no more values exist
    For intRows = 1 To Sheets(1).Cells(count, 18).Value 'Loops through number of rows needed
        Sheets(1).Cells(count + 1, 18).EntireRow.Insert 'Inserts rows
    Next intRows
    count = count + Sheets(1).Cells(count, 18).Value + 1    'Updates count variable
Wend

End Sub

BD

 

[jakecam]

The following code should do it.

Sub insertRows()

Dim i As Integer, j As Integer, rowsToInsert As Integer
colLetter = "A"  'change this to the column letter you are using, i.e. "R"

records = Application.CountA(ActiveSheet.Range(colLetter & ":" & colLetter))

'sum all rows to add
For i = 1 To records
    allRowsToInsert = allRowsToInsert + Range(colLetter & i).Value
Next i

'reitrate all records & rows to insert

For i = 1 To (records + allRowsToInsert)
    rowsToInsert = Range(colLetter & i).Value
        
    For j = (i + 1) To (i + rowsToInsert)
            
       If j = i Then
          'insert below cell
          Range(colLetter & j).Offset(1,0).EntireRow.Insert 
    
       Else
          'insert where j is
          Range(colLetter & j).EntireRow.Insert 

       End If
        
    Next j

        'bring i to the next number on record
         i = i + rowsToInsert     
Next i

End Sub

 

[jakecam]

Correction:

Sub insertRows()

Dim i As Integer, j As Integer, rowsToInsert As Integer
rowsToInsert = 0
colLetter = "A"  'change this to the column letter you are using, i.e. "R"

records = Application.CountA(ActiveSheet.Range(colLetter & ":" & colLetter))

'sum all rows to add
For i = 1 To records
    allRowsToInsert = allRowsToInsert + Range(colLetter & i).Value
Next i

'reitrate all records & rows to insert

For i = 1 To (records + allRowsToInsert)
    rowsToInsert = [COLOR=red]Range(colLetter & i).Value - 1[/color]
        
    For j = (i + 1) To (i + rowsToInsert)
            
       If j = i Then
          'insert below cell
          Range(colLetter & j).Offset(1,0).EntireRow.Insert 
    
       Else
          'insert where j is
          Range(colLetter & j).EntireRow.Insert 

       End If
        
    Next j

        'bring i to the next number on record
         i = i + rowsToInsert     
Next i

End Sub