Ensure that filter is null

Topic

New Reply

Is there a requirement to select a workbook or worksheet to ensure that the filter (if there is one) is null?

Reply | Quote

Replies

Who would impose that requirement? Gordon Brown?

Reply | Quote

No, it is a habit that I have acquired, but was wondering if the selection was needed?

Reply | Quote

No, you can use code like this:

Workbooks(“OtherWorkbook.xls”).Worksheets(“SomeSheet”).ShowAllData

There is no need to activate Otherworkbook.xls or SomeSheet.

Reply | Quote

Brilliant, Thanks!

Reply | Quote

But:

(I always get cunfused with this “selection” business)

To sort a range, like so:

Code:

    Set y1a = w1a.Range("A2:R" & x1a)
        y1a.Sort Key1:=Range("K2"), Order1:=xlAscending, Key2:=Range("L2"), Order2:=xlAscending, Header:=xlNo

where w1a is the worksheet, that has to be active, right??

No “actual selection” is made, but if the sheet is not active, I get:

Reply | Quote

No, w1a doesn’t have to be the active sheet. The problem here is that the sort keys Range(“K1”) and Range(“L2”) do refer to the active sheet because you don’t tell VBA that they are on w1a. But of course you can’t sort w1a on keys on another sheet! You must specify explicitly that the sort keys are on w1a:

Set y1a = w1a.Range(“A2:R” & x1a)
y1a.Sort Key1:=w1a.Range(“K2”), Order1:=xlAscending, Key2:=w1a.Range(“L2”), Order2:=xlAscending, Header:=xlNo

Reply | Quote

Arh, got’ya! Thankyou very much!!

Reply | Quote

Another little’un if I may:

Set v2 = Workbooks.Open(Filename:=”C:UsersNathanDocumentsmybook.xls”, ReadOnly:=True)

This then becomes the active wb, at front, so I currently use an activate command to flick back to my prime wb. Is there any way to open workbooks but not have them becoming active, to front?

Reply | Quote

Is there any way to open workbooks but not have them becoming active, to front?

No, when you open a workbook it will always become the active workbook. You could hide it immediately after opening it, by using

ActiveWindow.Visible = False

If you don’t want to hide it, you’ll have to reactivate the previously active workbook explicitly.

Reply | Quote