3

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.

Improve this question

2 Answers 2

Reset to default
2

Range(rngAdData) is feeding a range to a range. Just use rngAdData.AdvancedFilter

It's the same idea on your second problem. Use this syntax instead.

With ActiveWorkbook.Worksheets("....").Sort .SetRange Selection

With that said you should be using another means of getting your desired range other than using Select or Selection statements. Something like this should work better

Dim rngAdData As Range Dim sht As Worksheet, bottomMostRow As Long, rightMostColumn As Long Set sht = ActiveSheet With sht bottomMostRow = .Cells(1, 1).End(xlDown).Row rightMostColumn = .Cells(1, 1).End(xlToRight).Column Set rngAdData = .Range(.Cells(1, 1), .Cells(bottomMostRow, rightMostColumn)) End With
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

you blew it wide open for me, thanks so much. it's been a while since my last vba project. just a tiny question though, are .Select statements bad?
@mango They are slow and not reliable. You're much better off explicitly saying what you want purely in code rather than relying on changes in the cursor position. You code won't be vulnerable to someone clicking their mouse in the middle of execution and messing everything up. It also causes the screen to change which is slow too.
1

Try below code :

Sub sample() Dim rng As Range Range("A1").Select Set rng = Range("A1").CurrentRegion rng.AdvancedFilter xlFilterInPlace, Sheets("sheet1").Range("E7:H8"), False End Sub
Improve this answer

5 Comments

thanks so much for the help. I'm getting a compile error though. says argument not optional at: rngAdData.AdvancedFilter
do you want to filer with any criteria ?
yes i do, i'm sorry for omitting: rngAdData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= Sheets("...").Range("E7:H8"), Unique:=False
make sure you have removed rounded brackets. The code works fine at my end.
If you're trying that code as written I don't think you can filter on a single cell. In fact you need more than one row at least. Needs a header row and some data.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.