Invalid qualifer

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!

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.

---
Regards,
Norman



"davegb" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

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

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

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!

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 wrote:
> 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.
>
> ---
> Regards,
> Norman
>
>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >