HELP- Macro Stopped Working

[Delta Star]

I don't know why but this piece of code has stopped working correctly. Once it has set the filter and selected those with 0, it not only clears those cells with a 0 but all the others as well.

What have I done wrong?

Range("A1:F1").Select
Range("F1").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Range("A2:F" & LastRowA + 1).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:F" & LastRowA + 1).Select
Range("F2").Activate
Selection.AutoFilter Field:=5, Criteria1:=0
Selection.ClearContents
Selection.AutoFilter

 

[VoG]

Try

Selection.SpecialCells(xlCellTypeVisible).ClearContents

 

[Delta Star]

I recorded the macro again and it came up with a warning message about it being too complex. Sorted it numericall and it now works.

 

[JamesW]

Hi,

Try this:

    LastRowA = Range("A" & Rows.Count).End(xlUp).Row
    If ActiveSheet.AutoFilterMode = False Then Range("A1:F1").AutoFilter
    Range("A1:F1").AutoFilter Field:=5, Criteria1:=0
    Range("A2:F" & LastRowA).SpecialCells(xlCellTypeVisible).ClearContents

 

[JackDanIce]

Try:

Sub AliensHaveStolenMyShorts ()
Dim i As Long
 
Application.ScreenUpdating = False
 
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
i = Range("F" & Rows.Count).End(xlUp).Row
 
With Range("A1:F" & i)
 .AutoFilter
 .AutoFilter field:=5, Criteria1:=0
End With
 
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).ClearContents
ActiveSheet.AutoFilterMode = False
 
Application.ScreenUpdating = True
 
End Sub

 

[JamesW]

Sub AliensHaveStolenMyShorts ()

Nice one Jack