Cut a range between 2 cells containing found data
 

I normally can get to where I want with Excel VBA code but I have hit
the wall with this one and can't find a solution.

I have a list in Excel which contains 5 distinct ranges of
information. I want to split these 5 ranges and cut/copy to 5 separate
sheets. The actual moving of the data etc is not the problem. The
problem that I can't solve is:

basically I want to: select and cut all the rows beginning with a row
containing a cell with certain text and ending with a
row containing a cell with another specified text. I need to do this
repeatedly, and the number of rows between these two finds differs and
will differ at different times I run this macro.

Does anybody have a code suggestion that will enable me to do this?

I have been trying with the following:

Sub Range()
Range(Cells.Find("Air Jack System"), Cells.Find("Air Jack
System")).EntireRange.Cut
End Sub

But, I keep getting errors and it just wont work.

Any help or suggestions will be greatly appreciated. I hope this makes
sense. If not I will try to clarify it.

Many Thanks

Cut a range between 2 cells containing found data
 

Don't call a procedure Range, you will make the Range object error out.

Sub Ranges()
Dim rngStart As Range
Dim rngEnd As Range
Dim sFirst As String
Set rngStart = Cells.Find(What:="Air Jack System", _
Lookat:=xlWhole)
If rngStart Is Nothing Then
MsgBox "Start value not found)"
Else
sFirst = rngStart.Address
Set rngEnd = Cells.FindNext(rngStart)
If rngEnd Is Nothing Or sFirst = rngEnd.Address Then
MsgBox "End value not found"
Else
Range(rngStart, rngEnd).Cut
End If
End If
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
I normally can get to where I want with Excel VBA code but I have hit
the wall with this one and can't find a solution.

I have a list in Excel which contains 5 distinct ranges of
information. I want to split these 5 ranges and cut/copy to 5 separate
sheets. The actual moving of the data etc is not the problem. The
problem that I can't solve is:

basically I want to: select and cut all the rows beginning with a row
containing a cell with certain text and ending with a
row containing a cell with another specified text. I need to do this
repeatedly, and the number of rows between these two finds differs and
will differ at different times I run this macro.

Does anybody have a code suggestion that will enable me to do this?

I have been trying with the following:

Sub Range()
Range(Cells.Find("Air Jack System"), Cells.Find("Air Jack
System")).EntireRange.Cut
End Sub

But, I keep getting errors and it just wont work.

Any help or suggestions will be greatly appreciated. I hope this makes
sense. If not I will try to clarify it.

Many Thanks