select the first row after filter is applied or not the first

[agas]

Hello,

I am trying to do a filter and delete the rest of the data.

So lets say
<TABLE style="WIDTH: 84pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=112 border=0><COLGROUP><COL style="WIDTH: 42pt" span=2 width=56><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 42pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=56 height=20>AAP</TD><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 42pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=56>FA2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AAP</TD><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">FA2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AYT</TD><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">FA2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AYT</TD><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">FA2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>IMG</TD><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">FA2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>IMG</TD><TD class=xl60 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">FA2011
</TD></TR></TBODY></TABLE>
When i filter aap the data starts from third row and when i filter ayt data starts from first row.

when i the macro with record funtion

Sub deleteFact()
ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
        "AYT", "DGT", "GBB", "IMG",), _
        Operator:=xlFilterValues
    Range("A18").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    ActiveSheet.Range("$A$2:$AA$17").AutoFilter Field:=1
End Sub

It sets the range starter for the frist raw that i choose. How can i make it select and delete the remaining data ?

 

[Andrew Poulsom]

Try:

Sub Test()
    With ActiveSheet
        .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, _
            Criteria1:=Array("AYT", "DGT", "GBB", "IMG"), Operator:=xlFilterValues
        .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range("$A$2:$AA$17").AutoFilter Field:=1
    End With
End Sub

 

[agas]

Thank you very much

One more thing Can i set the array here with user input ?

 

[Andrew Poulsom]

What do you mean by user input? You can use a Variant array created from a range:

http://www.mrexcel.com/forum/showthread.php?t=429788