Excel のこととか楽天とか いろいろ書いてみます・・・

【Excel/VBA】AutoFilter, Filtering For Blanks or Not Blanks


 Excel things in English



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.

 Filter for blank cells




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 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. 

 Set the macro on the Ribbon




 About personal macro workbook




 About setting macros on the ribbon