Thread: Inserting row based on condition

Hey All,

I've sifting through some older posts on here and I came across some code that has helped me get a basis for what I'm trying to accomplish.

Below is the working code but I want to insert a line wherever I encounter a number. I have a range of cells in column "A" with invoices listed and at the end of each customer I have the total. At the total line (the number) I want to insert a line. Right now I can't figure out how get the cell value to recognize a number from a non-number data type (bolded area)

TIA!

Sub InsertByCriteria()
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
Application.EnableEvents = False
    
For i = lastrow To 2 Step -1
    If Cells(i, 1).Value > 0# _
        Then Rows(i).EntireRow.Insert Shift:=xlDown
Next i
 
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

XL2008,

Please post your workbook, or a sample of it - scroll down and see "Manage Attachments".

XL2008, be sure to go back and EDIT that first post, click GO ADVANCED (if necessary) and highlight all that code...then click the # icon to wrap the code in [/code] tags. It's a forum requirement and it makes the code readable in native format.

========

Then try this:

Sub InsertByCriteria()
Dim LastRow As Long, i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.EnableEvents = False

    For i = LastRow To 2 Step -1
        If IsNumeric(Cells(i, 1).Value) Then Rows(i).EntireRow.Insert Shift:=xlDown
    Next i

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

How about this little tweak:

Sub InsertByCriteria()
Dim LastRow As Long, i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.EnableEvents = False

    For i = LastRow To 2 Step -1
        If IsNumeric(Cells(i, 1).Value) Then Rows(i + 1).EntireRow.Insert Shift:=xlDown
    Next i

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub