MOVING A VARIABLE RANGE

I have this problem trying to move a range to another worksheet
For intance I need to highlight and move (also delete leftover blank
spaces) the range containing event column "rev" to sheet 2. The
solution should be able to take any range size (i.e x rows of event
"rev")

SHEET1

NAME EVENT DATE
EUROCOMM VAMB 14/07/2005
EUROCOMM VAMB 14/07/2005
EUROCOMM VAMB 14/07/2005
EUROCOMM rev 18/10/2005
EUROCOMM rev 25/08/2005
EUROCOMM rev 01/08/2005
EUROCOMM rev 28/07/2005
EUROCOMM init 23/12/2005
EUROCOMM init 15/12/2005
EUROCOMM init 14/12/2005
EUROCOMM init 13/12/2005
EUROCOMM init 12/12/2005

See if one of the canned solutions at Ron de Bruin's site won't work here:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"sylink" <[email protected]> wrote in message
news:[email protected]...
> I have this problem trying to move a range to another worksheet
> For intance I need to highlight and move (also delete leftover blank
> spaces) the range containing event column "rev" to sheet 2. The
> solution should be able to take any range size (i.e x rows of event
> "rev")
>
> SHEET1
>
> NAME EVENT DATE
> EUROCOMM VAMB 14/07/2005
> EUROCOMM VAMB 14/07/2005
> EUROCOMM VAMB 14/07/2005
> EUROCOMM rev 18/10/2005
> EUROCOMM rev 25/08/2005
> EUROCOMM rev 01/08/2005
> EUROCOMM rev 28/07/2005
> EUROCOMM init 23/12/2005
> EUROCOMM init 15/12/2005
> EUROCOMM init 14/12/2005
> EUROCOMM init 13/12/2005
> EUROCOMM init 12/12/2005
>

Assuming that there are no columns separating the data, and its starts at A1
of Sheet 1, the following code will work:

Sub MoveData()

Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set wb = ActiveWorkbook
Set ws1 = wb.Worksheets("Sheet1")
Set ws2 = wb.Worksheets("Sheet2")

ws1.Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Cut
ws2.Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Set wb = Nothing
Set ws1 = Nothing
Set ws2 = Nothing

End Sub

--
Kevin Backmann


"sylink" wrote:

> I have this problem trying to move a range to another worksheet
> For intance I need to highlight and move (also delete leftover blank
> spaces) the range containing event column "rev" to sheet 2. The
> solution should be able to take any range size (i.e x rows of event
> "rev")
>
> SHEET1
>
> NAME EVENT DATE
> EUROCOMM VAMB 14/07/2005
> EUROCOMM VAMB 14/07/2005
> EUROCOMM VAMB 14/07/2005
> EUROCOMM rev 18/10/2005
> EUROCOMM rev 25/08/2005
> EUROCOMM rev 01/08/2005
> EUROCOMM rev 28/07/2005
> EUROCOMM init 23/12/2005
> EUROCOMM init 15/12/2005
> EUROCOMM init 14/12/2005
> EUROCOMM init 13/12/2005
> EUROCOMM init 12/12/2005
>
>

Thanks Kevin. The solution is expected to move only the part shown
below because the evevnt is "rev".Others to remain in sheet1:

NAME EVENT DATE
EUROCOMM rev 18/10/2005
EUROCOMM rev 25/08/2005
EUROCOMM rev 01/08/2005
EUROCOMM rev 28/07/2005