Excel 2007 - Deleting Rows from a Table with AutoFilter
 

Given a Table1 that looks like this:
column1 column2
1 USA
2
3 USA
4 USA
5
6 UK
7 Germany
8
9 Finland
10 Russia
11 USA
12 Japan


And using Excel 2007, I need to delete all rows who have a column2 cell who
value is blank.
Sounds simple, googled a number of examples that suggest to
AutoFilter the list with an "=" criteria to match blanks:
Then using
SpecialCells(xlCellTypeVisible).EntireRow.Delete

And this code:
Sub Macro1()
Dim table As ListObject
Set table = ActiveSheet.ListObjects("Table1")
table.Range.Select
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e
End Sub

I get this error:
Run-time error '1004': Delete method of Range class failed.

Any Ideas on what I am doing wrong?
TIA
Tom

Excel 2007 - Deleting Rows from a Table with AutoFilter
 

See this page for a example
http://www.rondebruin.nl/delete.htm


Try this one without filter first
http://www.rondebruin.nl/specialcells.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"TomC" wrote in message ...
Given a Table1 that looks like this:
column1 column2
1 USA
2
3 USA
4 USA
5
6 UK
7 Germany
8
9 Finland
10 Russia
11 USA
12 Japan


And using Excel 2007, I need to delete all rows who have a column2 cell who
value is blank.
Sounds simple, googled a number of examples that suggest to
AutoFilter the list with an "=" criteria to match blanks:
Then using
SpecialCells(xlCellTypeVisible).EntireRow.Delete

And this code:
Sub Macro1()
Dim table As ListObject
Set table = ActiveSheet.ListObjects("Table1")
table.Range.Select
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e
End Sub

I get this error:
Run-time error '1004': Delete method of Range class failed.

Any Ideas on what I am doing wrong?
TIA
Tom