Excel/Filter macro II



  Let me rephrase my question because I think you misunderstood something.

   I meant by the macro "not breaking", that I didn't want any hard-coded cells in the macro, as I am developing the workbook and inserting rows, columns, etc, thus the data table is moving around.  

   This macro will always be applied to the same column in the table (the fourth one over), but that column may be in column D, E, F, etc, as I delete and insert worksheet columns.

   So if there is any filter set, anywhere in the table, I want that filter to be cleared, and then apply the "no-blanks-visible" filter to the column of interest, which will always be the one titled "Description", wherever it happens to end up as I move the entire table around.

   Thanks for your quick answer, though I haven't used it because I think you misunderstood me.


This will just filter out rows that have no blank cells in the Description column for the existing autofilter.

first it removes all filter conditions
Then it applies a filter on the 4th column of the autofilter that hides cells(rows) that are blank in that column.  

Sub FilterBlanks()
Dim rng As Range
Dim af As AutoFilter
On Error Resume Next
 Set af = ActiveSheet.AutoFilter
 Set rng = af.Range
 Debug.Print rng.Address
On Error GoTo 0
If rng Is Nothing Then
 MsgBox "Manually apply a filter to your data"
 Exit Sub
On Error Resume Next
On Error GoTo 0
 rng.AutoFilter Field:=4, Criteria1:="<>"
End If
End Sub

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 About.com. All rights reserved.