Clear a "moving" range offset from Target

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

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
Click to expand...

try:

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


Regards
Claus B.
 
try:
Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents


Regards
Claus B.
Click to expand...


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.
Click to expand...

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.
 
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.
Click to expand...

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


Regards
Claus B.
 
Hi Howard,

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


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


Regards
Claus B.
--
Click to expand...

I understand, I had a little button on the sheet that ran an enable events macro on the sheet. I used it during my tests, because of all the errors I was getting.

Works just fine now, I appreciate the help.

Howard
 
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
 
If your lists are contiguous, why not name them?
Click to expand...

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
 
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
Click to expand...

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.
Click to expand...

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/1fp9b8cb08pehdy/Drop Down List Copied Garry_v.xlsm?dl=0

Howard
 
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/1fp9b8cb08pehdy/Drop Down List Copied Garry_v.xlsm?dl=0

Howard
Click to expand...

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
 
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?
Click to expand...

Can't answer that, was just using a code from a seemingly trusted site to make quick work of naming several ranges.

Best to scrap that and go with everything you use and know to work.

Howard
 
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
 
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!)
Click to expand...

Question.

DD_2_6 DD_2_7 DD_2_8 DD_2_9 DD_2_10

Am I correct in that the Hdr names are incorrect for these columns?

The offset formula for column DD_2_6 is Hdr_7.

DD_2_6 selected in the D2 drop down returns column G list.
DD_2_10 selected in the D2 drop down returns a blank list.

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

DD_2_6 DD_2_7 DD_2_8 DD_2_9 DD_2_10

Am I correct in that the Hdr names are incorrect for these columns?

The offset formula for column DD_2_6 is Hdr_7.

DD_2_6 selected in the D2 drop down returns column G list.
DD_2_10 selected in the D2 drop down returns a blank list.

Howard
Click to expand...

Nice catch! All DV lists return values, just all wrong for DD_2 lists.
(DD_2_10 returns Hdr2_6 list, the remainders are off by 1 on my copy!)
My bad.., I wasn't paying attention to the sort order in NameManager
when I re-defined your existing names. I apologize!!!

So I corrected the range defs, inserted a narrow col between DD_1/DD_2
lists, and uploaded a revised file.

--
Garry

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

DD_2_6 DD_2_7 DD_2_8 DD_2_9 DD_2_10

Am I correct in that the Hdr names are incorrect for these columns?

The offset formula for column DD_2_6 is Hdr_7.

DD_2_6 selected in the D2 drop down returns column G list.
DD_2_10 selected in the D2 drop down returns a blank list.

Howard
Click to expand...


I was able fix the typo and drop down in D2 returns stuff correctly.

Where I can't follow your footprints is adding these in the next 5 columns K:O.

I have added cell F2 to the DD_List and I have can select all the Hdr's K thru O in the drop down.

These:
DD_3_11 DD_3_12 DD_3_13 DD_3_14 DD_3_15

Trouble is making the connection to the data in the lists to these selections in the drop down.

Howard
 
I was able fix the typo and drop down in D2 returns stuff correctly.

Where I can't follow your footprints is adding these in the next 5
columns K:O.

I have added cell F2 to the DD_List and I have can select all the
Hdr's K thru O in the drop down.

These:
DD_3_11 DD_3_12 DD_3_13 DD_3_14 DD_3_15

Trouble is making the connection to the data in the lists to these
selections in the drop down.

Howard
Click to expand...

No problem! Have another look at the latest revision...

I added the DD_3 lists plus revised the list values (with a formula) to
better reflect the list they belong to instead of their col label. This
required adding a col-relative, row-absolute name for the headers
row...

=HdrRow&"-"&ROW()-1

This will make adding new lists easier to populate with values.


I renamed the cols to reflect they ref cols. (eg: DD1_1 to Col1_1)


<more info>
I use the NameBox (left of FormulaBar) to enter fully absolute range
names. (Hdrs, cols, DV lists) I use the NameManager addin for
everything else.

The DV lists use fully absolute global names. They're names have been
revised to reflect they ref lists. (eg: DD_2 to DD2_Lists)

I hope you find this more helpful!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
No problem! Have another look at the latest revision...

I added the DD_3 lists plus revised the list values (with a formula) to
better reflect the list they belong to instead of their col label. This
required adding a col-relative, row-absolute name for the headers
row...

=HdrRow&"-"&ROW()-1

This will make adding new lists easier to populate with values.


I renamed the cols to reflect they ref cols. (eg: DD1_1 to Col1_1)


<more info>
I use the NameBox (left of FormulaBar) to enter fully absolute range
names. (Hdrs, cols, DV lists) I use the NameManager addin for
everything else.

The DV lists use fully absolute global names. They're names have been
revised to reflect they ref lists. (eg: DD_2 to DD2_Lists)

I hope you find this more helpful!<g>
Click to expand...

Okay, and thanks for doing the workbook.

You can only dummy it down just so much, the rest is up to me to absorb it.<g>

It is a good study piece for me, it will be helpful for sure.

I'll ponder away and thanks again.

May be back with a couple more what-if's, the code looks pretty intense! But at least I know what it is supposed to do, which will be helpful.

Howard
 
Back
Top