PDA

View Full Version : [SOLVED] Insert row based on string



gj0519
04-21-2005, 02:48 PM
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
04-21-2005, 03:01 PM
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
04-21-2005, 03:16 PM
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
04-21-2005, 07:05 PM
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
04-21-2005, 10:13 PM
Oh! Gotcha! :D

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
04-22-2005, 10:59 AM
Thanks Zack,

That is what I needed it to do.

Glenn