MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Finding and Copying


Posted by Kathi Budden on February 01, 2001 7:49 AM

DDE
1
2
3
4
5
KILL
DDE
6
7
8
9
10
KILL
DDE
11
12
13
14
15
KILL

I need to loop through and find DDE and then find KILL, select the row after DDE through KILL, and cut into new sheet.

So at the end of sheet 1,
1-5 are left in Sheet 1
6-10 are in Sheet 2
11-15 are in Sheet 3
and so on

PLEASE HELP!!!!!


Posted by Faster on February 01, 2001 4:03 PM

Sub DDE_KILL()
'make a backup of your work before you start
'++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++
'you should make a backup of your spreadsheet before you start
'++++++++++++++++++++++++++++++++++++++++++++
'++++++++++++++++++++++++++++++++++++++++++++

'+++++++++++++++assumptions++++++++++++++++++
'Name your DDE KILL sheet "Main"
'you are starting on the first DDE
'there are no blank cells between DDE and KILL

'good luck

Dim RangeStart
RangeStart = ""
Dim RangeEnd
RangeEnd = ""

Do While Selection <> ""
Do Until RangeEnd <> Empty
ActiveCell.Select
If Selection = "DDE" Then
Selection.Offset(1, 0).Select
RangeStart = ActiveCell.Address
ElseIf Selection = "KILL" Then
Selection.Offset(-1, 0).Select
RangeEnd = ActiveCell.Address
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop

Range(RangeStart, RangeEnd).EntireRow.Select

Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
'the name of your spreadsheet must be "main" to work
Sheets("Main").Select

Range(RangeEnd).Select
Selection.Offset(2, 0).Select
RangeStart = ""
RangeEnd = ""
Loop
End Sub