Excel- Inserting a varying number of rows

  • Thread starter Thread starter Breeder
  • Start date Start date
B

Breeder

Hi Everybody,

In an Excel 2003 worksheet, I am trying to automate the insertion of
varying number of blank rows in between existing rows of data (I thin
it is called intercalation?). The number of rows to be so inserted is
function of a value located in a cell in another column (but the sam
row) of the same worksheet.

The first column in the attached example is called a "RowID", an
identifies the row number which plants were grown in (this is from
plant breeding experiment). The second column is "EarNum", and thi
number is the number of plants in that RowID that produced
harvestable product. It will vary from zero to 15. For example, I wan
to use the cell value of B2 to direct the insertion of 5 blank row
directly beneath it, and so on.

I have made workable macros to insert a constant number of blank row
in the past. What I haven't figured out yet, is a good way of pointin
to the EarNum value and then using it in a macro to insert that numbe
of blank rows directly beneath its row.

Sincerely appreciate any help!
Breede
 
Not a lot of error checking:-

Assumes data in Col A and values in Col B - Will finish the first time it hits a
blank in Col A

Sub InsertBlankRows()

Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
Dim Rng As Range
Dim LastRw As Long

LastRw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "B"), Cells(LastRw, "B"))

On Error Resume Next
For r = Rng.Rows.Count To 1 Step -1
If Rng.Rows(r).Offset(0, -1) <> "" Then
numRows = Rng.Rows(r).Value
Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert
Else: Exit For
End If
Next r

Application.ScreenUpdating = True

End Sub
 
Hi Breeder,

As long as you are going to select the cell with the number, something like
this should do what you want.

Sub Whatever()
Dim i As Integer
i = ActiveCell.Value
For i = 1 To i
ActiveCell.EntireRow.Insert
Next
End Sub

HTH
Regards,
Howard
 
Back
Top