Re: Insert multiple rows

Tech-Archive recommends: Fix windows errors by optimizing your registry

Option Explicit
Sub testme()
Dim wks As Work***
Dim iRow As Long
Dim HowManyRows As Variant
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("Sheet1")
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
HowManyRows = .Cells(iRow, "B").Value
If IsNumeric(HowManyRows) Then
'some minor testing
If HowManyRows > 1 _
And HowManyRows < 100 Then
.Rows(iRow + 1).Resize(HowManyRows - 1).Insert
'do you want the names copied down to the
'inserted rows?
'.Cells(iRow + 1, "A").Resize(HowManyRows - 1).Value _
' = .Cells(iRow, "A").Value
End If
End If
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

sdg8481 wrote:

Hi,

I have a excel workbook that i need to insert a number of rows based on a
value in a cell, for example;

A1 B1
Bob Smith 5
John Smith 3

I need to insert another 4 rows below bob smith (to give 5 in total) and
another 2 rows below John Smith (to make 3 in total), etc.....

Is this possible, any ideas?????

Thanks in advance

--

Dave Peterson
.

Quantcast