VBA autofilter - filter blanks only

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
Hi

I've recorded a macro which filters blanks in a sheet.

However, I need to modify it, so that it deletes the blanks from the range in the sheet regardless of the number of rows.

Does anyone know how to modify the code below, please? Also, is there a good tutorial on VBA autofilters that you would recommend, so that I can see how to modify these myself?

TIA

Code:
Sub Autofilter()'


    Rows("1:1").Select
    Selection.Autofilter
    ActiveSheet.Range("$A$1:$D$145").Autofilter Field:=1, Criteria1:="="
    Rows("40:40").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$D$112").Autofilter Field:=1
    ActiveSheet.AutoFilterMode = False
    
End Sub
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
I found this code online, which should do the same thing (delete the blank rows in column A).

And it does in a separate workbook, with dummy data, but not the real data.

So any thoughts on how to modify the code above would still be greatly appreciated.


Code:
Sub DeleteRows()
    Range("A:A").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
 
Last edited:

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
I also found this code, which works.

However, any thoughts on modifying the recorded macro would be appreciated.

I also found out that I had to change the Header in the Sub below to remove duplicates from "xlYes" to "xlNo" and that fixed the problem where one set of data was appearing twice! Phew!!!

Code:
Range("A2:b100000").RemoveDuplicates Columns:=1, Header:=xlNo
Code:
[COLOR=#101094][FONT=Consolas][FONT=inherit]Sub[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] DeleteRows[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]()[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]With[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] ActiveSheet
[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]AutoFilterMode [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]False[/FONT][/FONT][/COLOR][COLOR=#858C93][FONT=Consolas][FONT=inherit]'remove filter [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]With[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Range[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"A:G"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]AutoFilter Field[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]:=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]1[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit],[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Criteria1[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]:=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"="[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]On[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Error[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Resume[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Next[/FONT][/FONT][/COLOR][COLOR=#858C93][FONT=Consolas][FONT=inherit]' for the case when there is no visible rows[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Resize[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit](.[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Rows[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Count [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]-[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]1[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]).[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Offset[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]1[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit],[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]0[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]).[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]SpecialCells[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]xlCellTypeVisible[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]).[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]EntireRow[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Delete
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]On[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Error[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]GoTo[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]0[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]End[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]With[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] 
[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]AutoFilterMode [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]False[/FONT][/FONT][/COLOR][COLOR=#858C93][FONT=Consolas][FONT=inherit]'remove filter[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]End[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]With[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]End[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Sub[/FONT][/FONT][/COLOR]
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,628
Office Version
365, 2010
Platform
Windows, Mobile
Are you sure that your cells are truly blanks and not formulas returning "".
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
463
Yes, definitely - because the data is pasted as values rather than formulas.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
That could be the problem. If you paste a cell containing "" (as the result of a formula) as values, the cell isn't considered truly blank. So you'll need the autofilter and delete visible cells.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,628
Office Version
365, 2010
Platform
Windows, Mobile
Can't remember if this works after you have done a PasteSpecial (and on my phone so can't test) but what happens with...
Code:
Sub DeleteRows()
With  Range("A:A")
  .Value = .Value
  .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
It should work for definite if you used this method rather than the PasteSpecial Values at the start.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,007
Messages
5,465,976
Members
406,458
Latest member
Barboza Babcock

This Week's Hot Topics

Top