Drop Downs in cells B2 & D2 (more to come, maybe 5 + or so).

Code does what I want except the ClearContents which I have commented out.
(Ebable Events is commented out and I am using .Select for testing)

When a selection is made in the drop down, a list pertaining to that selection is copied to the column to the right of the drop down.

I am having trouble clearing that copied list when the next selection is made.
The list are various rows long and the column needs to be clean prior to the next list copied. Can't quite zero in on it, its just me and the late night I suppose!

The Drop Downs will be in every other column in row 2, for however many there will be.

Thanks.
Howard

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

Dim rngFound As Range
Dim aRowCount As Long, _
aColumn As Long, _
tRowCount As Long, _
tColumn As Long

Dim myFnd As String

myFnd = Target

'Application.EnableEvents = False

' tColumn = Target.Offset(, 1).Column
' MsgBox tColumn
' tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row
' MsgBox tRowCount

' Target.Offset(, 1).Resize(tRowCount, tColumn).Select '.ClearContents
'Exit Sub

Set rngFound = Sheets("Sheet3").Range("AA1:AL1").Find(What:=myFnd , _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then

aColumn = rngFound.Column
aRowCount = Cells(Rows.Count, aColumn).End(xlUp).Row
aColumn = rngFound.Column

rngFound.Offset(1, 0).Resize(aRowCount).Copy Target.Offset(, 1)

Else
MsgBox "No match found."
End If
Application.EnableEvents = True
End Sub

Hi Howard,

Am Thu, 22 Jan 2015 04:39:26 -0800 (PST) schrieb L. Howard:

' Target.Offset(, 1).Resize(tRowCount, tColumn).Select '.ClearContents

try:

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

try:

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents


Regards
Claus B.


Seems I can't get a tRowCount value, that line errors out.


tRowCount = Target.Offset(, 1).Cells(Rows.Count).End(xlUp).Row

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents

Hi Howard,

Am Thu, 22 Jan 2015 08:39:37 -0800 (PST) schrieb L. Howard:

Seems I can't get a tRowCount value, that line errors out.

for me this works fine:

tColumn = Target.Offset(, 1).Column
MsgBox tColumn
tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row
MsgBox tRowCount

If tRowCount 1 Then _
Target.Offset(, 1).Resize(tRowCount - 1, 1).Select '.ClearContents


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

This is what I found to work.
I figured it out, the same as you indicate.

This works for me.

The tRowCount 1 seems a good idea which I will try out.

Thanks Claus.


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

Dim rngFound As Range
Dim aRowCount As Long, _
aColumn As Long, _
tRowCount As Long, _
tColumn As Long

Dim myFnd As String

myFnd = Target

Application.EnableEvents = False

tColumn = Target.Offset(, 1).Column
tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row

Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents

Set rngFound = Sheets("Sheet3").Range("AA1:AL1").Find(What:=myFnd , _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then

aColumn = rngFound.Column
aRowCount = Cells(Rows.Count, aColumn).End(xlUp).Row
aColumn = rngFound.Column

rngFound.Offset(1, 0).Resize(aRowCount).Copy Target.Offset(, 1)

Else
MsgBox "No match found."
End If
Application.EnableEvents = True
End Sub

Hi Howard,

Am Thu, 22 Jan 2015 09:04:55 -0800 (PST) schrieb L. Howard:

This is what I found to work.
I figured it out, the same as you indicate.

if you run into an error your events are disabled.
If I disable events I insert an error handler.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

Optionally with some error handling...

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$, rngList As Range
sName = IIf(msRngNames(Target.Column) = "", Target.Value, _
msRngNames(Target.Column))

On Error GoTo Cleanup
With Target.Offset(0, 1)
If sName < "" Then
.Resize(Range(sName).Rows.Count) = ""
End If
If Target < "" Then
Set rngList = Range(Target.Value)
.Resize(rngList.Rows.Count) = rngList
End If
msRngNames(Target.Column) = Target.Value '//store new value
End With

Cleanup:
Set rngList = Nothing
End Sub

--
Garry

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

On Thursday, January 22, 2015 at 11:37:53 AM UTC-8, GS wrote:
If your lists are contiguous, why not name them?

Hi Garry,

I had thought about using an array, (and named ranges) but knew someone would have to take me by the hand and show me left,right,up,down almost all the way.

Also, the lists will (most likely) be on a different sheet, so the hurdle of dynamic named ranges from another sheet is in the fray. I believe you told me once that is not a problem but I also remember the solution was nearly incomprehensible to me.

I'll start with the example you provided and see what I can do.

If you don't mind working with the mindless, standby for a few "what now's...??"

Howard

On Thursday, January 22, 2015 at 11:37:53 AM UTC-8, GS wrote:
If your lists are contiguous, why not name them?

Hi Garry,

I had thought about using an array, (and named ranges) but knew
someone would have to take me by the hand and show me
left,right,up,down almost all the way.

Also, the lists will (most likely) be on a different sheet, so the
hurdle of dynamic named ranges from another sheet is in the fray. I
believe you told me once that is not a problem but I also remember
the solution was nearly incomprehensible to me.

I'll start with the example you provided and see what I can do.

If you don't mind working with the mindless, standby for a few "what
now's...??"

Howard

Ok, Howard! It might be better, though, to send me the file so I can
just give you back a working solution. One concern is how to handle
dropdown values that have spaces if multi-word. Usually you can simply
replace spaces with underscores, but it's important to structure the
names and dropdown items to compliment going this way.

--
Garry

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

Ok, Howard! It might be better, though, to send me the file so I can
just give you back a working solution. One concern is how to handle
dropdown values that have spaces if multi-word. Usually you can simply
replace spaces with underscores, but it's important to structure the
names and dropdown items to compliment going this way.

--
Garry

Thanks, Garry.

Hopefully this won't be too much a task. Maybe a fairly simple example of all this named range stuff will not be lost on me.

Here is a basic workbook with two drop downs and I have run a named range maker code on the lists on sheet 2.

https://www.dropbox.com/s/1fp9b8cb08...ry_v.xlsm?dl=0

Howard

Ok, Howard! It might be better, though, to send me the file so I can
just give you back a working solution. One concern is how to handle
dropdown values that have spaces if multi-word. Usually you can
simply replace spaces with underscores, but it's important to
structure the names and dropdown items to compliment going this
way.

--
Garry

Thanks, Garry.

Hopefully this won't be too much a task. Maybe a fairly simple
example of all this named range stuff will not be lost on me.

Here is a basic workbook with two drop downs and I have run a named
range maker code on the lists on sheet 2.

https://www.dropbox.com/s/1fp9b8cb08...ry_v.xlsm?dl=0

Howard

Um.., clearly there's a flaw in the "CreateNames" routine because
code-selecting any given range selects all rows across all cols. What
gives with the RefersTo for the range names?

--
Garry

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

Here's my version...

https://app.box.com/s/23yqum8auvzx17h04u4f

Note that the list sheet is hidden. Also, its fully absolute ranges are
local scope and are used to define the dynamic lists. Since the lists
are on another sheet, their names are global for coding simplicity. (I
prefer they were also local scope, but that's a bit more complex to
manage!)

Have a look in NameManager to see how I've configured things. Note that
I replaced the hard-code address in your 'exit' line with a defined
name, and the list refs for the DVs are also defined name ranges. This
will permit using areas instead of a block of cells should you
want/need to move the lists around. Note that the DD_1/DD_2 headers
must each be contiguous but they don't have to be together. (You could
separate them with a col space between them without breaking anything!)

--
Garry

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