automatic unique value extract
 

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub

automatic unique value extract
 

Do you have a range named APL in that worksheet?

If you do, maybe:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub

If you don't, then what's the range you want filtered?

and more important...

Dim APL as iRange
compiles for me, but I've never seen that type of variable. What is it?



MikeF wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub

--

Dave Peterson

automatic unique value extract
 

Dave,
Thanx for the reply.
Yes, the source range is named APL.
And iRange is a typo.

Tried this as below, get the same 1004 error msg as preivous when adding a
new number in the APL [source] range.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim APL As Range

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub



"Dave Peterson" wrote:

Do you have a range named APL in that worksheet?

If you do, maybe:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub

If you don't, then what's the range you want filtered?

and more important...

Dim APL as iRange
compiles for me, but I've never seen that type of variable. What is it?



MikeF wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub

--

Dave Peterson

automatic unique value extract
 

Dave,

Your solution works, I had to adjust another typo [agh!].
Thank you!!

PS -- Now if I could just get the output range to be a another sheet!!

"Dave Peterson" wrote:

Do you have a range named APL in that worksheet?

If you do, maybe:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub

If you don't, then what's the range you want filtered?

and more important...

Dim APL as iRange
compiles for me, but I've never seen that type of variable. What is it?



MikeF wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub

--

Dave Peterson

automatic unique value extract
 

What is the range that APL points to?

When I did my simple test, I selected A1:A10 and named it APL. And it worked
fine.

MikeF wrote:

Dave,
Thanx for the reply.
Yes, the source range is named APL.
And iRange is a typo.

Tried this as below, get the same 1004 error msg as preivous when adding a
new number in the APL [source] range.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim APL As Range

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub

"Dave Peterson" wrote:

Do you have a range named APL in that worksheet?

If you do, maybe:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub

If you don't, then what's the range you want filtered?

and more important...

Dim APL as iRange
compiles for me, but I've never seen that type of variable. What is it?



MikeF wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub

--

Dave Peterson


--

Dave Peterson

automatic unique value extract
 

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Parent.Worksheets("sheet2").Range( "Ae1"), Unique:=True

End Sub

ps. You don't need the Dim statement.


MikeF wrote:

Dave,

Your solution works, I had to adjust another typo [agh!].
Thank you!!

PS -- Now if I could just get the output range to be a another sheet!!

"Dave Peterson" wrote:

Do you have a range named APL in that worksheet?

If you do, maybe:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Me.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Me.Range("Ae1"), Unique:=True

End Sub

If you don't, then what's the range you want filtered?

and more important...

Dim APL as iRange
compiles for me, but I've never seen that type of variable. What is it?



MikeF wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub

--

Dave Peterson


--

Dave Peterson

automatic unique value extract
 

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub

automatic unique value extract
 

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson

automatic unique value extract
 

Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?

Also ---
Do you have any other worksheet events firing?
No.

Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.

'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


"Dave Peterson" wrote:

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson

automatic unique value extract
 

This seemed to work ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

.Sort.SortFields.Add _
Key:=.Range("Ae1"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

With .Sort
.SetRange .Parent.Range("Ae1")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True

End Sub

Don't you have a header in AE1 from the advanced filter? I changed that .header
parm to xlyes.


MikeF wrote:

Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?

Also ---
Do you have any other worksheet events firing?
No.

Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.

'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

"Dave Peterson" wrote:

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson


--

Dave Peterson

automatic unique value extract
 

Dave,
Thanx again for your time and assistance.
The suggested code was not disruptive, but did not sort the output range.
- Mike

"Dave Peterson" wrote:

This seemed to work ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

.Sort.SortFields.Add _
Key:=.Range("Ae1"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

With .Sort
.SetRange .Parent.Range("Ae1")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True

End Sub

Don't you have a header in AE1 from the advanced filter? I changed that .header
parm to xlyes.


MikeF wrote:

Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?

Also ---
Do you have any other worksheet events firing?
No.

Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.

'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

"Dave Peterson" wrote:

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson


--

Dave Peterson

automatic unique value extract
 

I like the syntax for the old .sort (which still works in xl2007):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

With .Range("AE1").EntireColumn
.Sort key1:=.Columns(1), order1:=xlDescending, header:=xlYes
End With
End With
Application.ScreenUpdating = True

End Sub




MikeF wrote:

Dave,
Thanx again for your time and assistance.
The suggested code was not disruptive, but did not sort the output range.
- Mike

"Dave Peterson" wrote:

This seemed to work ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

.Sort.SortFields.Add _
Key:=.Range("Ae1"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

With .Sort
.SetRange .Parent.Range("Ae1")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True

End Sub

Don't you have a header in AE1 from the advanced filter? I changed that .header
parm to xlyes.


MikeF wrote:

Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?

Also ---
Do you have any other worksheet events firing?
No.

Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.

'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

"Dave Peterson" wrote:

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

automatic unique value extract
 

That's the trick Dave.
Completely rocks ... "quietly".
Thanx again for all your time and assistance ...!!

- Mike

PS -- Probably should've clarified --- am using xl 07.


"Dave Peterson" wrote:

I like the syntax for the old .sort (which still works in xl2007):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

With .Range("AE1").EntireColumn
.Sort key1:=.Columns(1), order1:=xlDescending, header:=xlYes
End With
End With
Application.ScreenUpdating = True

End Sub




MikeF wrote:

Dave,
Thanx again for your time and assistance.
The suggested code was not disruptive, but did not sort the output range.
- Mike

"Dave Peterson" wrote:

This seemed to work ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

.Sort.SortFields.Add _
Key:=.Range("Ae1"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

With .Sort
.SetRange .Parent.Range("Ae1")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True

End Sub

Don't you have a header in AE1 from the advanced filter? I changed that .header
parm to xlyes.


MikeF wrote:

Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?

Also ---
Do you have any other worksheet events firing?
No.

Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.

'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

"Dave Peterson" wrote:

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

automatic unique value extract
 

I could tell that by the .sort syntax.

The real problem is that I don't use xl2007 enough to know it!

Glad you got it working.

MikeF wrote:

That's the trick Dave.
Completely rocks ... "quietly".
Thanx again for all your time and assistance ...!!

- Mike

PS -- Probably should've clarified --- am using xl 07.

"Dave Peterson" wrote:

I like the syntax for the old .sort (which still works in xl2007):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

With .Range("AE1").EntireColumn
.Sort key1:=.Columns(1), order1:=xlDescending, header:=xlYes
End With
End With
Application.ScreenUpdating = True

End Sub




MikeF wrote:

Dave,
Thanx again for your time and assistance.
The suggested code was not disruptive, but did not sort the output range.
- Mike

"Dave Peterson" wrote:

This seemed to work ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

.Sort.SortFields.Add _
Key:=.Range("Ae1"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

With .Sort
.SetRange .Parent.Range("Ae1")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True

End Sub

Don't you have a header in AE1 from the advanced filter? I changed that .header
parm to xlyes.


MikeF wrote:

Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?

Also ---
Do you have any other worksheet events firing?
No.

Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.

'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

"Dave Peterson" wrote:

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

automatic unique value extract
 

Well, hate to bug you on a Sunday, but have just noticed a fairly major issue.

Whenever I run DATA / SUBTOTAL on range a1.x200, of which the named range
APL is one column, the descending sort on the filter's ouptut range [ae1/etc]
is mitigated back to APL's source order. Causes big problems.

Have tried numerous sort routines - one would think it's fairly simple.
But NOTHING works until Subtotals are removed. Then everything is fine
again [upon next change in APL].

Simple Objective - to subtotal the large range of date without affecting the
descending sort in the filter's ouptut range [ae1/etc].
Again, should be simple, but ........

Thanx in advance Dave, for any ideas you have.
- Mike

"Dave Peterson" wrote:

I could tell that by the .sort syntax.

The real problem is that I don't use xl2007 enough to know it!

Glad you got it working.

MikeF wrote:

That's the trick Dave.
Completely rocks ... "quietly".
Thanx again for all your time and assistance ...!!

- Mike

PS -- Probably should've clarified --- am using xl 07.

"Dave Peterson" wrote:

I like the syntax for the old .sort (which still works in xl2007):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

With .Range("AE1").EntireColumn
.Sort key1:=.Columns(1), order1:=xlDescending, header:=xlYes
End With
End With
Application.ScreenUpdating = True

End Sub




MikeF wrote:

Dave,
Thanx again for your time and assistance.
The suggested code was not disruptive, but did not sort the output range.
- Mike

"Dave Peterson" wrote:

This seemed to work ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

.Sort.SortFields.Add _
Key:=.Range("Ae1"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

With .Sort
.SetRange .Parent.Range("Ae1")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True

End Sub

Don't you have a header in AE1 from the advanced filter? I changed that .header
parm to xlyes.


MikeF wrote:

Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?

Also ---
Do you have any other worksheet events firing?
No.

Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.

'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

"Dave Peterson" wrote:

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

automatic unique value extract
 

If you're going to insert those subtotal rows via the data|subtotal, I'd
recommend that you put that unique range on a different worksheet.

And if you insert rows into that range that includes the APL range, then aren't
you going to end up with the values from the =subtotal()'s in the extracted
range?

That doesn't sound like a good idea to me.

MikeF wrote:

Well, hate to bug you on a Sunday, but have just noticed a fairly major issue.

Whenever I run DATA / SUBTOTAL on range a1.x200, of which the named range
APL is one column, the descending sort on the filter's ouptut range [ae1/etc]
is mitigated back to APL's source order. Causes big problems.

Have tried numerous sort routines - one would think it's fairly simple.
But NOTHING works until Subtotals are removed. Then everything is fine
again [upon next change in APL].

Simple Objective - to subtotal the large range of date without affecting the
descending sort in the filter's ouptut range [ae1/etc].
Again, should be simple, but ........

Thanx in advance Dave, for any ideas you have.
- Mike

"Dave Peterson" wrote:

I could tell that by the .sort syntax.

The real problem is that I don't use xl2007 enough to know it!

Glad you got it working.

MikeF wrote:

That's the trick Dave.
Completely rocks ... "quietly".
Thanx again for all your time and assistance ...!!

- Mike

PS -- Probably should've clarified --- am using xl 07.

"Dave Peterson" wrote:

I like the syntax for the old .sort (which still works in xl2007):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

With .Range("AE1").EntireColumn
.Sort key1:=.Columns(1), order1:=xlDescending, header:=xlYes
End With
End With
Application.ScreenUpdating = True

End Sub




MikeF wrote:

Dave,
Thanx again for your time and assistance.
The suggested code was not disruptive, but did not sort the output range.
- Mike

"Dave Peterson" wrote:

This seemed to work ok for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
With Me
.Range("APL").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("Ae1"), Unique:=True

.Sort.SortFields.Add _
Key:=.Range("Ae1"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

With .Sort
.SetRange .Parent.Range("Ae1")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True

End Sub

Don't you have a header in AE1 from the advanced filter? I changed that .header
parm to xlyes.


MikeF wrote:

Dave,
My last msg was incorrect.
First off, I'm back on the routine that outputs to the same page, just for
temporary ease of use.
It's not the cursor that moves, it's merely the screen that "moves" so the
output range can be viewed every time the worksheet routine fires. The cursor
actually stays where it was. I could live with that if the worksheet routine
fired only when I changed a cell in the APL range.
But have discovered the real problem is that the worksheet routine fires
every time ANY cell on the sheet is changed. The source sheet, where APL
resides, is also a data-entry sheet for hundreds of cells, so it's quite
disruptive every time ANYTHING changes.
I guess the question is --- is there any way to have the worksheet routine
fire ONLY when something in APL changes?
Or if not - can the worksheet routine fire "quietly"?

Also ---
Do you have any other worksheet events firing?
No.

Did you add anything else to the worksheet_change event?
Yes, a routine that sorts the output in descending order.

'ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"Ae1")SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("Ae1")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

"Dave Peterson" wrote:

I couldn't get the cursor to move to a different cell.

Do you have any other worksheet events firing?

Did you add anything else to the worksheet_change event?

MikeF wrote:

Dave,
Thank you, that's great.
If I may bother you for one more detail [today!] that just came up as a
result of this ...

Now the active cell moves to the output range, as opposed to staying within
the source range where I made the change, which is what would be ideal.
Is there any way to make the routine "run quiet", thereby not moving to the
output range/sheet?

"MikeF" wrote:

Sort of a re-post:

Am looking to automatically extract unique values when anything in the
source list changes.

Have placed the following code at the Worksheet level, but it returns the
error msg "Method 'Range' of Object 'Worksheet" failed" .
Can anyone assist?
Thanx in advance.
- Mike

Private Sub Worksheet_Change(ByVal Target As Range)

Dim APL As IRange

Range("APL").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Ae1"), Unique:=True

End Sub



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson