Macro to add rows between cells - new at VBA
 

I have a spreadsheet at work that contains all the schedules for our
SAP scheduling program. This spreadsheet is used for recovery
procedurs for the specific jobs. The problem with the spreadsheet is
that each schedule has END as it's last line, and the next line is the
name of the next schedule. I would like to add two blank rows between
END and the next schedule name. I have a macro that does a find for
END, and then inserts two rows, but the rows go above the END. As I am
new to VBA (never needed to use it until now, so now I'm trying to self
teach), I'm not sure how to get the rows to go under the END cell.
Also, I'm not sure how to have it work for each END in the spreadsheet,
not just one at a time. Any help would be greatly appreciated!

Joey

Macro to add rows between cells - new at VBA
 

activecell.offset(1,0).Resize(2).EntireRow.Insert

--
Regards,
Tom Ogilvy


"jmcnelis" wrote in message
ups.com...
I have a spreadsheet at work that contains all the schedules for our
SAP scheduling program. This spreadsheet is used for recovery
procedurs for the specific jobs. The problem with the spreadsheet is
that each schedule has END as it's last line, and the next line is the
name of the next schedule. I would like to add two blank rows between
END and the next schedule name. I have a macro that does a find for
END, and then inserts two rows, but the rows go above the END. As I am
new to VBA (never needed to use it until now, so now I'm trying to self
teach), I'm not sure how to get the rows to go under the END cell.
Also, I'm not sure how to have it work for each END in the spreadsheet,
not just one at a time. Any help would be greatly appreciated!

Joey

Macro to add rows between cells - new at VBA
 

Thanks a lot for the response, Tom! I appreciate it, and it worked
exactly as I wanted it to. Is there a way to set it up so it will look
for each END in the sheet, or do I have to run the macro manually?
Thanks again!

Joey

Macro to add rows between cells - new at VBA
 

Assume END appears in column A.

Sub AddLines()
Dim c as Range, firstAddress as String
With Worksheets(1).Columns(1).Cells
Set c = .Find(What:="end", _
After:=Worksheets(1).Range("A1"), _
lookin:=xlValues, Lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.offset(1,0).Resize(2).EntireRow.Insert
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If
End With
End Sub


--
Regards,
Tom Ogilvy



"jmcnelis" wrote in message
oups.com...
Thanks a lot for the response, Tom! I appreciate it, and it worked
exactly as I wanted it to. Is there a way to set it up so it will look
for each END in the sheet, or do I have to run the macro manually?
Thanks again!

Joey