Advanced Filter List Range from within a Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to have a macro perform an advanced filter of my worksheet data. If I perform the filtering manually, the dialog box automatically calculates the list range for me. Is there a way to have the macro caluclate the extent of the list, and then use that information in the advanced filter call? Here's a sample of my code

Sheets("All Issues").Selec
Range("A1").Selec
Range("A1:P729").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=
Range("T1:T2"), Unique:=Fals

I got this code by recording a macro while I performed the advanced filter. I could set the range to something arbitrarily large like P1400, but that method does not seem very sophisticated (to say nothing of having to edit the macro if the list grows beyond 1400 lines). I have not seen an alternative in any of the other postings.

Thanks in advance for your help
Aaron
 
Hi Aaron.

Sorry about the unrelated response. You're obviously an
expert where I'm a mere novice. Would appreciate your help
in this question that's beyond me:

I have a cell containing a drop-down list box containing 3
values and three other cells next to it, each containing
option buttons. id like to group the cells with option
buttons together and have each button correspond to one of
the values in the drop-down list when checked and show
that value in the cell drop-down cell. hope the ? was
clear. I would REALLY appreciate the help. thanks!

regards,
Bill


-----Original Message-----
I would like to have a macro perform an advanced filter
Click to expand...
of my worksheet data. If I perform the filtering
manually, the dialog box automatically calculates the list
range for me. Is there a way to have the macro caluclate
the extent of the list, and then use that information in
the advanced filter call? Here's a sample of my code:
Sheets("All Issues").Select
Range("A1").Select
Range("A1:P729").AdvancedFilter
Click to expand...
Action:=xlFilterInPlace, CriteriaRange:= _
Range("T1:T2"), Unique:=False

I got this code by recording a macro while I performed
Click to expand...
the advanced filter. I could set the range to something
arbitrarily large like P1400, but that method does not
seem very sophisticated (to say nothing of having to edit
the macro if the list grows beyond 1400 lines). I have
not seen an alternative in any of the other postings.
 
You can use the current region:

Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("T1:T2"), _
Unique:=False
 
Hi Aaron

Use the CurrentRegion property . "The current region is a range bounded by
any combination of blank rows and blank columns"(VBA Help file), in other
words your list, however long it may be.

Dim Rng as Range
Sheets("All Issues").Select
Set Rng =Range("A1").CurrentRegion
Rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("T1:T2"), Unique:=False

Regards

Paul




Aaron Vowell said:
I would like to have a macro perform an advanced filter of my worksheet
Click to expand...
data. If I perform the filtering manually, the dialog box automatically
calculates the list range for me. Is there a way to have the macro
caluclate the extent of the list, and then use that information in the
advanced filter call? Here's a sample of my code:
Sheets("All Issues").Select
Range("A1").Select
Range("A1:P729").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("T1:T2"), Unique:=False

I got this code by recording a macro while I performed the advanced
Click to expand...
filter. I could set the range to something arbitrarily large like P1400,
but that method does not seem very sophisticated (to say nothing of having
to edit the macro if the list grows beyond 1400 lines). I have not seen an
alternative in any of the other postings.
 
Debra
You certainly know your stuff. Now, my one button macro execution produces some really cool results
Thanks for your help
Aaron
 
Back
Top