VBA to filter multiple sheets

[Zimbo]

Hi All,

I have a macro that filters all the sheets in a workbook based on a cell entry on sheet 1. The filter runs through Columns 1.

This works quite well(other than flicker issues).

The problem is I also need the filter to run through column K and show rows that have an empty cell.(i.e UnInvoiced) I have tried the below code, but it shows the empty formula rows below all data not the uninvoiced rows.

ElseIf RLetter = "Uninvoiced" And w.AutoFilterMode Then
w.Unprotect Password:="test"
w.Range("K3").AutoFilter Field:=11, Criteria1:=""

Please Help

Cheers

Adam

[MatrixMan]

Hi Zimbo .. others may have a different view, but for me, it's difficult to help without seeing the full code and trying to work out what's happening with 3 lines of code, those being only part of a conditional statement ... well, it could be anything really and may not even be in these lines. If you'd like to post it or prefaerably attach your workbook, I'll take a look for you. Are you applying these filters on A and then K? Depending on your data, they may be incompatible ..

On the flicker issues when filtering sheets - have you used:

Application.ScreenUpdating = False

Just don't forget to turn it back on again ..

Cheers, MM.

[Zimbo]

Hi All,

Accidently posting newer post is correct.

[Zimbo]

Hi MM,

I have managed to fix the flicker issues ( I was calling macros in the wrong places). Please find my code for the sheet sort below.
I am either sorting by Column A or Column K, but should not sort Column K unless Column A has a value. I have tried the sort criteria as both ="" and ="=".

Sub DoFilters()
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim w As Worksheet, RLetter As String
Dim Crit As String
Crit = Sheets("TOTALS").Range("DateRange").Value
RLetter = Crit
For Each w In Worksheets

If w.Index > 2 Then
If RLetter = "Year To Date" And w.AutoFilterMode Then
w.Unprotect Password:="test"
w.AutoFilterMode = False
w.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True


ElseIf RLetter = "Uninvoiced" And w.AutoFilterMode Then
w.Unprotect Password:="test"
w.Columns("K").AutoFilter Field:=1, Criteria1:="", Visibledropdown:=True
w.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True

''w.Range("A1").SpecialCells(xlCellTypeLastCell).AutoFilter Field:=11, Criteria1:="="

Else

w.Unprotect Password:="test"
w.Range("A1").End(xlDown).AutoFilter Field:=1, Criteria1:=Crit, Visibledropdown:=True
w.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True

On Error Resume Next
End If

'w.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
Call Refresh_Click
End Sub

The section "Uninvoiced" is the one I am having issues with.

All help is appreciated.

Cheers

Adam

[MatrixMan]

If I understand correctly that you're trying to filter first on col A for non-blanks and then any blanks in col K, try this:

ElseIf Crit = "Uninvoiced" And w.AutoFilterMode = True Then
    w.Range("A1").AutoFilter Field:=1, Criteria1:="<>", visibledropdown:=True
    w.Range("A1").AutoFilter Field:=11, Criteria1:="=", visibledropdown:=True

I'm not sure why you've got specialcells etc in there; if the autofilter is on the range for the filter is implied and you don't need to specify it.

Hope that helps. MM.