Excelのこととか色々

マクロをどんな Excel ファイルにも実行できるように設定して、3秒かかる操作も1秒で完了。あといろいろ書いてみます・・・

【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

 

 

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. 

 Set the macro on the Ribbon

 

---------------------------------------------------------------------

 

 About personal macro workbook

 

www.tuna-kichi.com

 

 About setting macros on the ribbon

www.tuna-kichi.com

 

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

 

 

 

 

PVアクセスランキング にほんブログ村 Excelのこととか色々 - にほんブログ村 にほんブログ村 子育てブログ 幼稚園児育児へ にほんブログ村 英語ブログ 初心者英語へ にほんブログ村 IT技術ブログ VBAへ