Inserting a row

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

I found this code in a thread posted on 2/18, and modified
it slighty, as follows:

Sub test()
Dim Rng As Range
FindString = "1"
Set Rng = Range("a:a").Find(What:=FindString,
LookAt:=xlPart)
While Not (Rng Is Nothing)
Rng.EntireRow.Insert
Set Rng = Range("a" & Rng.Row + 1 & ":a" &
Rows.Count) _
.Find(What:=FindString, LookAt:=xlPart)
Wend
End Sub

First, thanks to the person who posted it. It's coming
closer than any other code I've tried to doing what I
need. However, I do have a question about it. I noticed
that it always seems to insert a row after two lines, even
when there is no 1 in column A. Basicially, I am trying
to group rows together that share a common employee ID
number. A 1 was inserted whenever a new employee ID
appears. The majority of the lines I am trying to
separate do come in pairs, but some contain three lines,
and the code is not going down three lines to insert a
row. Is there a way to make it do this? Thanks-
 
Try this version:

Sub testFIND()
Dim Rng As Range
FindString = "1"
Set Rng = Range("a:a").Find( _
What:=FindString, _
After:=Range("A65536"), _
LookAt:=xlPart)
While Not Rng Is Nothing
Rng.EntireRow.Insert
Set Rng = Range("a" & Rng.Row + 1 & _
":a" & Rows.Count) _
.Find(What:=FindString, _
After:=Range("A65536"), _
LookAt:=xlPart)
Wend
End Sub
 
Back
Top