Auto insert a number of rows

[Guest]

Hi

In Excel 2000 I need to find a method of inserting a number of blank rows.

My sheet has a list of names in column A. Columns B to P are headed as a
number of different items and there is an entry under each heading. If the
entry is either 'Y' or a value greater than 0 I need to insert a blank line
below the name in a so that I can transpose the entries from the row into a
column. Having done this for the first name I have to repeat this process
for the list of names until complete. Each name will have a different number
of entries under the headigs B to P.

Thanks for any assistance.

Gareth

 

[Edwin Tam]

Hope below is what you're looking for...

'***************************
Sub insert_rows()
Dim tmp_address As String
Dim tmp%, tmp2 As Single, counter As Single
Dim tmp3
tmp_address = Selection.Address(False, False)
With ActiveSheet.Range(tmp_address)
For tmp2 = .Rows.Count To 1 Step -1
For tmp = 2 To .Columns.Count
tmp3 = .Rows(tmp2).Cells(tmp).Value
If Val(tmp3) > 0 Or UCase(tmp3) = "Y" Then
.Rows(tmp2 + 1).EntireRow.Insert Shift:=xlShiftDown
End If
Next
Next
End With
End Sub
'***************************


To use the macro:
1) Select the range to scan, from column one (A) to the last column (P?)
2) Run the macro.

The macro considers the value from the second column to the end of the
selected range. Whenever it sees a value >0 or = "Y", a new row will be
inserted below.

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com



*********************************************************************

 

[Guest]

This was really helpful Edwin.

Thanks a lot.

I don't know if you can help further but now I have inserted the rows I want
to put the column header of the cells that are greater than 0 or contain
something other than 'N'in the rows of the column next to the block
originally selected. I was going to use an IF statement but the number of
rows against each name varies.

If you can do this I will be extremely grateful.

Regards

Gareth

 

[Guest]

Hi Edwin

The code you supplied inserts one more line than I require for each staff
member. I've tried inserting to reduce the number of lines by one but have
failed miserably. Could you advise how to modify the code so that it
produces one less line ?

Thanks

Gareth

 

[Dave Peterson]

In my testing, Edwin's code worked perfectly.

Are you sure you selected the correct range before you ran the macro?

This variation of Edwin's code was easier for me:

Option Explicit
Sub insert_rows2()

Dim LastRow As Long
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iRow As Long
Dim iCol As Long

With ActiveSheet
FirstRow = 2 'headers in 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

FirstCol = .Range("b1").Column
LastCol = .Range("p1").Column

For iRow = LastRow To FirstRow Step -1
For iCol = LastCol To FirstCol Step -1
If (IsNumeric(.Cells(iRow, iCol).Value) _
And .Cells(iRow, iCol).Value > 0) _
Or (UCase(.Cells(iRow, iCol).Value) = "Y") Then
.Rows(iRow + 1).Insert
.Cells(iRow + 1, "A").Value = .Cells(1, iCol).Value
End If
Next iCol
Next iRow
End With
End Sub