Can someone please hint at what i might be doing wrong here? For now I am effectively trying to do a Ctrl-A command to do a select all on a block of data in vba. Then i want that selection to be saved as a range, so that I can use it later.
Dim rngAdData As Range
.....
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set rngAdData = Selection
Range(rngAdData).AdvancedFilter Action:=xlFilterInPla.... //<----
The last line gives me a run-time error '1004': Method 'Range' of object 'Global' failed
When I do it the following way, it works
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).AdvancedFilter Action:=xlFilterInPla....
But doing it this way is cumbersome because I need to use that range again here
With ActiveWorkbook.Worksheets("....").Sort
.SetRange Range(Selection) //<---
The line being pointed to gives me that same error.
Best Answer
Range(rngAdData)
is feeding a range to a range. Just userngAdData.AdvancedFilter
It's the same idea on your second problem. Use this syntax instead.
With that said you should be using another means of getting your desired range other than using
Select
orSelection
statements. Something like this should work better