I think you'll have to use a real range for that criteria.
And I'm not sure that this is quicker than reading the range into an array and
looping through that -- but it was pretty darn fast even with 10000 rows.
Option Explicit
Sub testme()
Dim TempWks As Worksheet
Dim CritWks As Worksheet
Dim wks As Worksheet
Dim myRng As Range
Dim LastRow As Long
Dim LastCol As Long
Dim HowMany As Long
Application.ScreenUpdating = False
Set wks = Worksheets("Sheet1")
Set CritWks = Worksheets.Add
Set TempWks = Worksheets.Add
With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myRng = .Range("A1", .Cells(LastRow, LastCol))
'copy the headers
.Rows(1).Copy _
Destination:=CritWks.Range("a1")
End With
'put the value you want in K2 of the criteria worksheet
CritWks.Range("K2").Value = 4
'copy the 4's to the new sheet
myRng.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=CritWks.UsedRange, _
CopyToRange:=TempWks.Range("A1"), _
Unique:=False
'just uniques in column A
With TempWks
.Range("A:A").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:="", _
CopyToRange:="", _
Unique:=True
End With
With TempWks
'avoid the header in row 1
HowMany = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells.Count - 1
End With
With Application
.DisplayAlerts = False
CritWks.Delete
TempWks.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With
MsgBox HowMany
End Sub
ker_01 wrote:
>
> Currently, I am using:
>
> Sheet3.Range("A1:A10000").AdvancedFilter _
> Action:=xlFilterCopy, _
> CopyToRange:=Sheet2.Range("A1"), _
> Unique:=True
>
> This generates a list of the unique values from my source list. While I can
> get to my real end result through several additional steps, I suspect that
> I'm underutilizing AdvancedFilter, and am hoping for a few pointers on how to
> get it to return some more specific information.
>
> Specifically,
> (1) I do have a criteria that I'd like to use without placing it in a cell
> range (Select unique values from A1:A10000 where the corresponding value in
> Column K is '4'). It appears that I can put the value 4 in a cell and
> reference it, but can I just use a variable which is =4? If so, what is the
> appropriate syntax?
> e.g.,
> CriteriaRange:= (Sheet1.range(K:K).value = 4)
>
> (2) Ultimately, what I really want is a count of the unique values that meet
> my criteria, so can I use a Count against the returned range, without sending
> it to a physical page location? Perhaps I can make a named range that
> references an array instead of a range, and use that to generate a count in a
> second step?
>
> Initially I was using formulas to get my unique counts, but using a
> sumproduct formula (actually, several dozen of them) against an array this
> large was just taking too long, and initial tests with AdvancedFilter
> indicated it would be much faster.
>
> I appreciate any syntax hints with the AdvancedFilter, or even confirmation
> on whether or not what I'm attempting is even possible with AdvancedFilter.
>
> My backup option is to just loop the page and sort the data into a 2D array
> (while keeping track of whether each value is unique), but AdvancedFilter
> seemed like a more direct option (no loops, etc)
>
> Thank you,
> Keith
--
Dave Peterson
|
Similar Threads
