Norman,
Somehow I posted my previous reply to your reply incorrectly. It's
posted above, under the previous other posts, not below your previous
post. If any of that makes any sense?! In other words, see above. Your
previous solution worked great. It was K. Dales solution I was having
trouble with, based on my original code. I was asking him, or anyone,
to show me how to specify the correct range when using that approach,
for my own education and edification. I'd already implemented yours
successfully. Thanks!

Norman Jones wrote:
Hi Dave,

See if this works for you:

Sub ShowAll()
Dim MacRec As Object
Dim Sh As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim rw As Long, col As Long
Dim RngCrit As Range

Set RngCrit = Sheets("Macro Records").Range("S5:Z6")

For Each Sh In ActiveWorkbook.Worksheets
With Sh
Set Rng1 = Sh.Range("B3")
rw = Rng1.End(xlDown).Row
col = .Cells(rw, Columns.Count). _
End(xlToLeft).Offset(, 2).Column
Set Rng2 = Range(Rng1, .Cells(rw, col))
Rng2.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=RngCrit, _
Unique:=False
End With
Next Sh
End Sub

---
Regards,
Norman



"davegb" wrote in message
oups.com...
Thanks!
Now I'm getting an "argument not Optional" on
Sh.Range.AdvancedFilter...
I suspect that even though I selected the range, XL doesn't know which
one I want to filter. And there is no "selection" option for a range
object. So how do I tell it I want it to filter on the currently
selected range? I want to avoid using named ranges because they have to
apply across all worksheets and I need to filter this range on many
different worksheets, don't want to have to create unique range names
on each sheet if I can avoid it.

Sub ShowAll()
Dim MacRec As Object
Dim Sh As Worksheet

Set MacRec = ActiveWorkbook.Sheets("Macro Records")


For Each Sh In ActiveWorkbook.Worksheets
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight).Offset(0, 2)).Select
Sh.Range.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=MacRec.Range("S5:Z6"), Unique:=False
Range("a1").Select
Next Sh
End Sub

Any ideas?


davegb wrote:
This code gives me an invalid qualifier error:

Range("B4").Select

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight.Offset(0, 2))).Select

I want to select a range starting at B4, ending at the bottom of column
B, going to the right until the data ends, and then adding 2 more
columns to the range. Can someone point me in the right direction?
Thanks!