Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto populate data from a table into cells after a match is found | Excel Worksheet Functions | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Conditional format if cell match found in another range of cells | Excel Worksheet Functions | |||
Check if a value is found twice in a range | Excel Programming | |||
Range Name Found?? | Excel Programming |