If your lists are contiguous, why not name them? Then you can store the
name in an array and retrieve it based on Target.Column, replacing it
with the new value!

Option Explicit

'Dim an array large enough for future expansion
Dim msRngNames$(1 To 50) '//adjust as required


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2,D2")) Is Nothing Then Exit Sub

Dim sName$
sName = IIf(msRngNames(Target.Column) = "", Target.Value, _
msRngNames(Target.Column))
With Target.Offset(0, 1)
If sName < "" Then
.Resize(Range(sName).Rows.Count) = ""
End If
If Target < "" Then
.Resize(Range(Target.Value).Rows.Count) = Range(Target.Value)
End If
msRngNames(Target.Column) = Target.Value '//store new value
End With
End Sub

This avoids any errors if the array or Target is empty. The named
ranges can be dynamic!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion