Insert rows based on cell value

Microsoft Excel 2003 (full)
June 18, 2010 at 08:04:04
Specs: Windows XP
Hello,

Can someone help me with a script or something alternative to fix my issue ? I have a column with values of "OK" and numbers. I need a script that will go through the whole column and whenever it finds a number, it inserts the number of rows found starting with that row. For example: if the cell in row 7 has the value 3, i want to insert automatic 3 blank rows starting with row 7.

Thank you


See More: Insert rows based on cell value

Report •


#1
June 18, 2010 at 08:29:42
Hi,

If you have a number, say 3, on Row 7 and insert 3 rows, the number 3 is now on row 10

Is that the way you want it to work,
or is it like this:
If Row 7 has a number 3
and Row 8 has a number 5
after inserting 3 rows, the 3 is still on row 7 and the 5 is now on Row 11

Also:
which column contains the OK / numbers.
what are the first and last rows used for OK/numbers

Regards


Report •

#2
June 19, 2010 at 02:09:37
Hi,

Thanks for your reply. Yes you have a point there.
Let`s say i have a 3 on row 7 and a 5 on row 8. That means
that after this script runs and it finds the 3 on row 7, it will
insert 3 rows so that the 5 will be on row 11 now.

The column i have has only OK and numbers, so i need it that
when the script inserts the rows it doesn`t freeze if it find a
row that has neither OK nor a number, it has to continue after
the inserted rows.

If it is possible it would be usefull for the script that once it
finds the number 3 it could memorize that value, and replace
it with something like "fixed" and insert the rows before this
row. So that this alternative script would insert the 3 rows
before the row number 3 is on. So that after it runs one step
"fixed" would be on row 10 and 5 would be on row 11. Next
step it would insert 5 rows so that the new "fixed" would be on
row 16, with the old "fixed" on row 10 from the completion of
the old step.


Report •

#3
June 19, 2010 at 02:11:42
Column E contains the OK/numbers, and i don`t know the exact
number of rows, it would be around 800 or so. I have 29 sheets
that i want it to apply to, either as a whole or run it on each
sheet at a time.

Report •

Related Solutions

#4
June 19, 2010 at 05:56:03
Hi,

Here are two routines that I think match your two scenarios.

InsBelow just adds rows below the numbers in column E and leaves the numbers as they were.
InsAbove adds rows above the row containing the number and replaces the number with 'fixed'
For testing purposes I changed the number like this: '3' becomes 'fixed (3)'
You can change this in the code if you like.

As you want to use these routines on different worksheets (but within the same workbook, I suggest that the two routines (macros) are placed in a module, and then run from a toolbar button.

From any worksheet in the workbook, right-click the worksheet name tab and select 'View Code'
In the left hand pane of the Visual Basic window that opens there is the 'Project Explorer' (if this is not visible click View - Project explorer from the Visual Basic menu bar).

In Project Explorer, the worksheet name you came from in your workbook will be highlighted.
Right-click and select Insert - Module (not Code module)
Double click the new module - typically 'Module1' under the Modules folder.
Paste the code into it:

Option Explicit

Public Sub InsBelow()
Dim lngStartRow As Long
Dim lngEndRow As Long
Dim m As Integer
Dim n As Long

With ActiveSheet
    'work from bottom up
    'set start row
    lngStartRow = Range("E" & CStr(Application.Rows.Count)).End(xlUp).Row
    'set end row
    lngEndRow = 1
    
    'loop through all used rows in column E starting at bottom
    For n = lngStartRow To lngEndRow Step -1
        If IsNumeric(Range("E" & CStr(n)).Value) Then
            'insert rows below
            For m = 1 To Range("E" & CStr(n)).Value
                Rows(n + 1).Insert
            Next m
        End If
    Next n
End With
End Sub

Public Sub InsAbove()
Dim lngStartRow As Long
Dim lngEndRow As Long
Dim intInsert As Integer
Dim m As Integer
Dim n As Long

With ActiveSheet
    'work from bottom up
    'set start row
    lngStartRow = Range("E" & CStr(Application.Rows.Count)).End(xlUp).Row
    'set end row
    lngEndRow = 1
    
    'loop through all used rows in column E starting at bottom
    For n = lngStartRow To lngEndRow Step -1
        If IsNumeric(Range("E" & CStr(n)).Value) And _
                            Range("E" & CStr(n)).Value <> "" Then
            'save value
            intInsert = Range("E" & CStr(n)).Value
            'replace number with 'fixed'
            Range("E" & CStr(n)).Value = "fixed (" & CStr(intInsert) & ")"
            'insert rows above
            For m = 1 To intInsert
                Rows(n).Insert
            Next m
        End If
    Next n
End With
End Sub

Select Save from the Visual Basic menu bar.
Use Alt+f11 (Alt key and function key #11 clicked together), to return to the main Excel window.
Right-click on a toolbar and select 'Customize' (at the bottom of the list).
In the Commands Tab, select 'Macros' from the left hand side.
On the right drag the smiley face 'Custom Button' icon to a toolbar.
Right click the new icon and select 'Assign Macro'.
Sellect InsAbove or InsBelow from the list, then 'OK' and 'Close'
Clicking the button will run the macro on the active worksheet.
(When 'Customize' is open you can right-click the button and select Change button image or Edit button image).

Repeat for the other macro - and change or edit the icon to distinguish the two.

Note that changes made by Macros cannot be undone with the Undo button.
Test these macros on backup copies of your data and always make a backup before running the macros.

Regards


Report •

Ask Question