VBA Filter and Delete

[JackDanIce]

Hi,

I currently filter my data and then use this bit of code to remove the filtered rows, whilst keeping the header:

With ActiveSheet
    .UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    .AutoFilterMode = False
End With

However, I'm now trying to change this to (ignore the defined range, purely for test purposes):

With Range("A1:C5")
    .AutoFilter
    .AutoFilter field:=2, Criteria1:="1"
    .SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count - 1).Rows.Delete
End With

Line in red is where it fails, I believe my Resize use is not correct, but unsure of what correct sytnax here should be.

Can anyone correct or suggest what to use in combination with SpecialCells(xlCellTypeVisible) so that the header is kept but the remainder of the visible rows are deleted?

Thanks,
Jack

 

[JackDanIce]

bump

 

[Sektor]

Try

.SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count - 1).[B][COLOR="Red"]EntireRow[/COLOR][/B].Delete

 

[JackDanIce]

Thank you Sektor but still receive error message on your version of:

Run-time error '1004':
Application-defined or object-defined error

Which is the same error message I get with my version

I think it's something I'm doing wrong with Resize but can't work out what..

 

[Sektor]

I guess the problem with ".Autofilter" line. Try this.

Sub G()
    
    With Range("A1:C5")
        [COLOR="Red"]If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter[/COLOR]
        .AutoFilter field:=2, Criteria1:="1"
        .SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count - 1).Rows.Delete
        .AutoFilter
    End With

End Sub

 

[JackDanIce]

Same error message.

I got this to work:

i = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:C" & i)
  .AutoFilter
  .AutoFilter field:=2, Criteria1:="1"
  .Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Delete
End With

Only issue is it deletes Row(i+1) but since that row has nothing in it, guess it'll do

 

[Sektor]

The issue was with Range("A1:C5"). It should be Range("A1:C1").

Sub G()
    
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    With Range("A1:C1")
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
        .AutoFilter field:=2, Criteria1:="1"
        Range("A2:C" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With

End Sub

 

[JackDanIce]

Thanks, works fine