Error message

[BadHorsie]

Hi all

I've written a little and supposedly simple code to insert rows between entries that are different.. i.e. what i have is something like this:

John 21 34
John 25 20
John 344 35
Peter 22 1
Peter 35 1

and I want it to look like this:

John 21 34
John 25 20
John 344 35

Peter 22 1
Peter 35 1

Here is my code:


Sub InsertRows()

Dim rownum As Integer
rownum = 1
Do
rownum = rownum + 1
If StrComp(Cells(rownum, 1), Cells(rownum - 1, 1), 1) <> 0 Then
Rows("rownum:rownum").Select
Selection.Insert Shift:=xlDown
End If
Loop While rownum < 100
End Sub

and it returns an error (at the Selection.Insert Shift:=xlDown line)
can anyone help me with this problem?

Thanks, Pat

[Jacob Hilderbrand]

Change the Rows line to this:


Rows(rownum & ":" & rownum).Select


rownum is a variable so don't put the quote around it.

[mdmackillop]

For this type of operation (like deleting duplicates), start at the bottom.

Sub InsertRows()

Dim rownum As Long
For rownum = 25 To 2 Step -1
If StrComp(Cells(rownum, 1), Cells(rownum - 1, 1), 1) <> 0 Then
Rows(rownum).Insert Shift:=xlDown
End If
Next
End Sub

[Charlize]

Another approach. Starting from top to bottom. Must take in account the changing of last row with data + adding of blank row.
Sub InsertRows()
Dim rownum As Long
Dim lastrow As Long
rownum = 1
'presumes sheet 1 is active. last row with data in
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Do
rownum = rownum + 1
If StrComp(Cells(rownum, 1), Cells(rownum - 1, 1), 1) <> 0 Then
Rows(rownum & ":" & rownum).Select
Selection.Insert Shift:=xlDown
'skip the blank row that you've added
rownum = rownum + 1
'recalculate the last row with data. A must be filled in.
lastrow = Range("A" & Rows.Count).End(xlUp).Row
End If
Loop While rownum < lastrow
End Sub
Charlize

[lucas]

I haven't tried your code Charlize but I compliment your use of comments...it really helps people who are trying to understand what is going on with the routine.