When we filter for blank cells.....
1. Click the pull-down button
2. Go down to the bottom
3. Check or uncheck "(Blanks)"
I don't like to click this ▼ because it is tiny, and sometimes "(Black)" is far away if the table is big, so I made macros and set it to the ribbon.
Macros
Case 1: AutoFilter is set from column A
Sub AutoFilter_Blanks()
Dim c As Long
c = Selection.Column 'Get column number of the selected cell
Range("A1").AutoFilter Field:=c, Criteria1:="="
End Sub
------------------------------------------------
This macro works well when AutoFilter is set from column A ONLY.
The sentence below is for filtering non-blanks.
Range("A1").AutoFilter Field:=c, Criteria1:="<>"
Case 2: Works for any table
（it should be.....）
Sub AutoFilter_Blanks_AnyTable()
Dim AFAdrss As String 'Store AutoFilter range
Dim FirstC As String 'Store the first column number of AutoFilter
Dim CrntField As String 'Store a column number of a selected cell
Dim c As String 'Store a column number for filtering
Dim FirstRng As String 'Store an address of the first cell of a column
Dim p As Variant 'Set as an array
'Check AutoFilter is set or not, and if not, this macro will end.
If ActiveSheet.AutoFilterMode = False Then
End
End If
With ActiveSheet.AutoFilter.Range
FirstC = .Column 'Get the first column number of AutoFilter
AFAdrss = .Address 'Get the range of AutoFilter
End With
'Calculating where a column number, which you want to filter,
is from the first column of the AutoFilter range
'Currently selected column number - the first column number +1
c = Selection.Column - FirstC + 1
'Use array and Split function to get the address of the first cell of Autofilter range
'The address of AutoFilter is stored like "$A1$:$Z$59" then separate it by a colon (:)
'using the Split function
'Set the first half for filtering sentence.
p = Split(AFAdrss, ":")
FirstRng = p(0)
Range(FirstRng).AutoFilter Field:=c, Criteria1:="="
'Below is for filtering for non-blanks
'Range(FirstRng).AutoFilter Field:=c, Criteria1:="<>"
End Sub
**********************************************************
Write this macro on the "Personal macro workbook" and set it to the ribbon.
Let the macro runs for any Excel books opened on your computer.
---------------------------------------------------------------------
About personal macro workbook
About setting macros on the ribbon
＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／
