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

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)



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>