VBA autofilter - filter blanks only

[Mr2017]

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

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

 

[Mr2017]

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.

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

 

[Mr2017]

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

Range("A2:b100000").RemoveDuplicates Columns:=1, Header:=xlNo

[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]

 

[MARK858]

Are you sure that your cells are truly blanks and not formulas returning "".

 

[Mr2017]

Yes, definitely - because the data is pasted as values rather than formulas.

 

[RoryA]

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]

Can't remember if this works after you have done a PasteSpecial (and on my phone so can't test) but what happens with...

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.