Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need a Macro To Add Rows in Excel

Status
Not open for further replies.
Jul 13, 2007
47
US
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
 
This may get you started:

If you have a value 5 in cell R1, this will insert 5 rows below R1
Code:
Dim intRows As Integer

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

Have fun.

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

Thanks
 



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...
Code:
  MsgBox "Hello, world!"
You could then include that in a procedure like...
Code:
Sub DisplayMessage()
  MsgBox "Hello, world!"
End Sub

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
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
 



Your error, "Invalid Outside Procedure"

needs,
Skip said:

Code must run within a PROCEDURE; either a Sub or a Function
per my example.

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
floridagunner,

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

Best of luck!

Code:
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
 
The following code should do it.

Code:
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
 
Correction:

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top