Invalid qualifer

[davegb]

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!

 

[Norman Jones]

Hi Dave,

Try something like:

Sub Tester()
Dim Rng1 As Range, Rng2 As Range
Dim rw As Long, col As Long

Set Rng1 = Range("B4")

rw = Rng1.End(xlDown).Row
col = Cells(rw, Columns.Count). _
End(xlToLeft).Offset(, 2).Column

Set Rng2 = Range(Rng1, Cells(rw, col))
Rng2.Select '<<<===== Optionally delete (See Below)

End Sub

It is rarely necessary to make selections, so consider deleting the line:
Rng2.Select

Most of what you might wish to do can be achieved by operating on the
(Rng2) range object variable.

 

[Guest]

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

 

[davegb]

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]

Norman,
Thanks for the help! It worked great. I know that selecting is slower
and less elegant, I just don't know yet how to do most of the things I
need without selecting! Must admit, I find XL VBA terribly confusing.
Probably because most of my previous programming was in very "old
fashioned" languages long before "objects" were invented. What reading
I did in the early days of OOP said it was supposed to make programming
easier, which I find to be entirely untrue. But then, of course,
computers were supposed to make our lives less complicated, at least
according to some pundits, when they first came along! At my age, I
should be used to being lied to by the "experts"!
What I ended up with is:

Sub Showall1()
Dim Rng1 As Range, Rng2 As Range
Dim lRow As Long, lCol As Long

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

Set Rng1 = Range("B4")


lRow = Rng1.End(xlDown).Row
lCol = Cells(lRow, Columns.Count). _
End(xlToLeft).Offset(, 2).Column


Set Rng2 = Range(Rng1, Cells(lRow, lCol))
Rng2.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=MacRec.Range("S5:Z6"), Unique:=False

End Sub

I appreciate your giving me a more elegant way to do the same thing.
I'm hoping I'm learning as I go, though some days, I wouldn't swear to
it. But it definitely helps when someone gives better approaches rather
than just correcting my code. Not that I don't appreciate all the help
offered by the experts here.
Thanks!

 

[Norman Jones]

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

 

[davegb]

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!