On Saturday, September 7, 2013 2:38:43 AM UTC-7, Claus Busch wrote:
Hi again,



Am Sat, 7 Sep 2013 11:03:04 +0200 schrieb Claus Busch:



you can do that also a bit easier (enlarge the code - I have only three

items in it):



or:



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address < "$H$1" Then Exit Sub



Dim c As Range

Dim myOffset As Integer

Dim rngBig As Range



Select Case Target.Value

Case "Swine"

myOffset = 4

Case "Diary"

myOffset = 3

Case "Beef"

myOffset = 2

End Select



With Range(Target & "x").SpecialCells(xlCellTypeConstants)

Set rngBig = Union(Range(Target & "x") _

.SpecialCells(xlCellTypeConstants), _

.Offset(columnOffset:=myOffset), _

.Offset(columnOffset:=myOffset + 1))

rngBig.Copy Range("I50").End(xlUp).Offset(1, 0)

End With





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

I like!!

Thanks Claus.