Insert row based on string

[gj0519]

I am trying to insert a row after a certain string is found and continue until the end . Here is what I have so far, but it does nothing yet. The column that I am searching for the value is "B". I will have 3 or 4 different values that I need to insert a row after they are found. I found this code from this site and just tried to manipulate it to work for me, but no luck.

Thanks,

glenn

Sub Insert()
Dim x As Long
Dim LastRow As String
Dim Index As String
With Sheets("Sheet1") 
LastRow = .Range("B1000").End(xlUp).Row
Index = Range("B" & LastRow).Value
For x = LastRow To 1001 Step -1
If .Range("B" & x).Value = Index Then
'Skip
Else
Index = Range("B" & x).Value
Range("B" & x + 1).Value = Index + 1
Range("B" & x + 1).EntireRow.Insert
End If
Next x
End With
End Sub

[Zack Barresse]

Hi, welcome to the board!!


Let's see, first of all, make sure your variables are correct. LastRow should be Long, not String. It's really unclear as to what you are trying to do in regards to where your data is. An uploaded example workbook would work best. Maybe you can try ...

Sub InsertRows()
    Dim x As Long, StartRow As Long
    With Sheets("Sheet1")
        StartRow = .Range("B1000").End(xlUp).Row
        For x = 1001 To StartRow Step -1
            Select Case Range("B" & x).Value
            Case "a", "b", "c", 1, 2, 3
                Range("B" & x).EntireRow.Insert
            End Select
        Next x
    End With
End Sub

This will work with all the Case's specified between the StartRow and LastRow.

[gj0519]

Thanks,

I have attached the workbook. Basically I have different loan types that are sorted in Asc order. I need to insert a row after each type. I tried your new code but it did not insert any rows. Probably cause my first posting was incorrect to begin with.

Glenn

[gj0519]

Zack,

After I looked at what I was doing I figured out why that code was not working.
Now realizing my first explanation of what I needed was vague, here is 1 more thing I'm not sure on how to get it to work.
That code does insert a row after each case instance it finds, but I only need 1 row added after the last record for any group. For example if column "B" has rows 1 - 5 with "a", then insert a row after the last one it finds and continue down the column inserting rows after any other group.
Hope this is more clear.
Thanks for the help ealier.

Glenn

[Zack Barresse]

Oh! Gotcha!

Use this ...

Option Explicit
Private Sub CommandButton1_Click()
    Dim x As Long, LastRow As Long
    With Sheets("All Loans")
        LastRow = 3
        For x = .Range("B65536").End(xlUp).Row To LastRow Step -1
            If .Range("B" & x) <> .Range("B" & x - 1) Then .Rows(x).Insert
        Next x
    End With
End Sub

Works for me. And for future reference, when Inserting or Deleting rows you ALWAYS need to start from the bottom and work up. The same goes for columns except that you are working from right to left. If not and you loop in VBA, when you perform your insert/delete you will skip rows/columns.


HTH Glenn

[gj0519]

Thanks Zack,

That is what I needed it to do.

Glenn