Cut a range between 2 found cells.

[HWA101]

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

 

[TonyJollans]

You don't give enough detail to be sure but you might be able to use an AutoFilter to get the rows you want. If so, you can cut and paste easily.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[mikej336]

Here is some code I use to clear a range... Basically I look for a cell I know will always contain a value and once I find and empty cell I clear the range in between..

You could do the same but look for your values...

Private Sub CommandButton3_Click()
'Clear All
Dim clearit As Range
Dim endrange As Range
Dim ACell, LastCell As Range

Dim RowIndex As Integer
RowIndex = 9
Set ACell = Range("A" & Trim(Str(RowIndex + 2)))

Do While Not IsEmpty(ACell)
RowIndex = RowIndex + 1
Set ACell = Range("A" & Trim(Str(RowIndex)))
Loop
Set endrange = Range("M" & Trim(Str(RowIndex + 2)))
Set clearit = Range("A9", endrange)
'MsgBox "the end " & Str(RowIndex)
clearit.Clear

End Sub

Hope it helps

Uncle Mike

 

[PHV]

Anyway, don't name a sub Range !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

When I see requurements that segment data to different sheets, it raises a question in my mind.

You can very easily pick off a subset of a range, using the INDEX, OFFSET to get the range subset, MATCH to get the range offset & COUNTIF to get the range depth.

You should leave your original range untouched and use a formula or some other technique to get the subset of data on your other sheets. SOURCE DATA should not be chopped up. That destroys most of its usefulness.



Skip,

_[red][/red]