automatic unique value extract

[MikeF]

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]

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]

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

 

[MikeF]

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]

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.

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

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?

 

[Dave Peterson]

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.

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!!

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]

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?

 

[Dave Peterson]

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?

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?

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

 

[MikeF]

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

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?

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?

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]

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.

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

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?

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?

:

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

 

[MikeF]

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

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.

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

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?

:

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]

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

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

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.

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

:

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?

:

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

 

[MikeF]

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.

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

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

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

:

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?

:

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]

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.

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.

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

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

:

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

:

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?

:

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

 

[MikeF]

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

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.

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.

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

:

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

:

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?

:

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]

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.

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

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.

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.

:

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

:

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

:

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?

:

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