Hi,

I want an AutoFilter over a couple of columns. So I did this:

Sub af()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1) ' Instead 1 "Sheet1" also
ws.Range("A1:D1").AutoFilter field:=1, Criteria1:="",
VisibleDropDown:=False

End Sub

Even a macro record didn't work as expected. Excel Version is 2k3 SP3

THX 4 Help

You would have found that your code worked the first time but not on repeated
runs of the code.

The workaround is to not use visibledropdown:=False when first turning
AutoFilter on. Turn AutoFilter on with all dropdowns visible then you can set
visibledropdown:=False for the filtered fields.

Before running the following code for the first time, manually turn off
AutoFilter.

This bug seems to be fixed in xl2007.

Sub af()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1) ' Instead 1 "Sheet1" also
With ws
If .AutoFilterMode = False Then
.Range("A1:D1").AutoFilter
End If
End With

ws.Range("A1:D1").AutoFilter Field:=1, Criteria1:="", visibledropdown:=False
End Sub

--
Regards,

OssieMac

Hi OssieMac,

it worked.

This bug seems to be fixed in xl2007.

And the bug not starting in column A too, I hope so.

CU, BR

chfa

If I'm applying a filter (with new criteria), I'll remove the old filter (who
knows where it's located on the worksheet???) and then reapply it where I want.

Option Explicit
Sub af()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
ws.AutoFilterMode = False
ws.Range("A1:D1").AutoFilter field:=1, _
Criteria1:="", VisibleDropDown:=False
End Sub

In fact, I don't like to let excel guess what the autofilter range should be.
If there are gaps in the rows, excel may not guess correctly:

If I know that column A is always used if the row is used, I'd use something
like:

Option Explicit
Sub af2()
Dim LastRow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)
With ws
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ws.AutoFilterMode = False
ws.Range("A1:D" & LastRow).AutoFilter field:=1, _
Criteria1:="", VisibleDropDown:=False
End With
End Sub


(Same thing with the number of columns.)



chfa wrote:

Hi,

I want an AutoFilter over a couple of columns. So I did this:

Sub af()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1) ' Instead 1 "Sheet1" also
ws.Range("A1:D1").AutoFilter field:=1, Criteria1:="",
VisibleDropDown:=False

End Sub

Even a macro record didn't work as expected. Excel Version is 2k3 SP3

THX 4 Help

--

Dave Peterson

You are correct Dave in not allowing VBA to guess, particularly with
autofilter, because who knows what the user has been doing before running the
code.

However, in testing your code I found that need to turn off autofilter
before finding the last row otherwise last row will always be the last
visible row of the autofiltered data and not necessarily the last row of data.

Sub af3()
Dim LastRow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)
With ws
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:D" & LastRow).AutoFilter field:=1, _
Criteria1:="", visibledropdown:=False
End With
End Sub

--
Regards,

OssieMac

You're absolutely right!

Thanks for the correction, OssieMac.

OssieMac wrote:

You are correct Dave in not allowing VBA to guess, particularly with
autofilter, because who knows what the user has been doing before running the
code.

However, in testing your code I found that need to turn off autofilter
before finding the last row otherwise last row will always be the last
visible row of the autofiltered data and not necessarily the last row of data.

Sub af3()
Dim LastRow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)
With ws
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:D" & LastRow).AutoFilter field:=1, _
Criteria1:="", visibledropdown:=False
End With
End Sub

--
Regards,

OssieMac

--

Dave Peterson

Hi Dave Peterson,

just a little improvement: EXCEL behaves ambiguous regarding the last
used cell. I prefer

Set lastCell = ThisWorkbook.Worksheets(shtName) _
.Range(ColName & ":" & ColName) _
.SpecialCells(xlCellTypeLastCell)

for finding the last Cell. The Rest is a loop looking for Value=""

LupusSilvae

If I know my data, I'd rather use some other technique than trusting what excel
thinks is the last cell.

And even though you've specified in your code to look at just a single column,
don't you still get the last used cell of the entire worksheet.

Lupussilvae wrote:

Hi Dave Peterson,

just a little improvement: EXCEL behaves ambiguous regarding the last
used cell. I prefer

Set lastCell = ThisWorkbook.Worksheets(shtName) _
.Range(ColName & ":" & ColName) _
.SpecialCells(xlCellTypeLastCell)

for finding the last Cell. The Rest is a loop looking for Value=""

LupusSilvae

--

Dave Peterson

Hi Dave,

you're right saying excel to behave like a bitch ;-). My snippet is
only a base indeed.
I agree that .SpecialCells(xlCellTypeLastCell) is only one method to
be followed by others to be sure at all.

BR
LS