Delete filter outcome

[DaveG93]

Hey,

I wrote a macro which first filters a table and then deletes these rows. That works fine, but the macro also deletes 2 more rows and I don't know why, since these two rows are not filtered if I use the filter itself without the deletion option.

Attached you'll find the macro, I hope someone can help me with this, 'cause I really have no clue

Sub AutofilterergebnisLoeschen()

Range("L2").Copy _
Destination:=Range("M2")

Range("M2").Value = "USD in EUR"



ActiveSheet.Rows(8).Delete
ActiveSheet.Columns("H:H").Delete

ActiveSheet.Rows(4).Delete
ActiveSheet.Columns("D:D").Delete

Range("M1").Select
ActiveCell.FormulaR1C1 = "=1.2"

Range("K3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=""US-DOLLAR"",RC[-7]*R1C13,RC[-7])"
    Selection.AutoFill Destination:=Range("K3:K2500"), Type:=xlFillDefault
    Range("K3:K2500").Select
    ActiveWindow.SmallScroll Down:=-2500


'** Dimensionierung der Variablen

Dim rngFilterRange As Range
Dim lngCriteriaCount As Long
Dim arrCriteria() As String
 
'** Anzahl der Kriterien festlegen
lngCriteriaCount = 4

 
'** Variable neu dimensionieren
ReDim arrCriteria(0 To lngCriteriaCount - 1)

 
'** Filterkriterien festlegen
arrCriteria(0) = "1500"
arrCriteria(1) = "1593"
arrCriteria(2) = "1600"
arrCriteria(3) = "9999999900"
 

'** Objektvariable setzen
Set rngFilterRange = ActiveSheet.Range("A1")

'** Autofilter setzen/ausführen
rngFilterRange.AutoFilter Field:=1, _
Criteria1:=arrCriteria(), _
Operator:=xlFilterValues



    'Löscht das Ergebnis des aktuellen Autofilters
    'also die aktuell angezeigten Zeilen !
    'Bereich der per Autofilter gefilterten Liste ist variabel !
    
    If ActiveSheet.AutoFilterMode Then
        If ActiveSheet.FilterMode Then
            If MsgBox("Sollen die Zeilen des aktuellen " & vbLf & _
            "Autofilterergebnisses komplett gelöscht werden ?", _
                vbQuestion + vbYesNo, "Zeilen Löschen") = vbYes Then
            With ActiveSheet.AutoFilter.Range.Offset(1)
                .Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1).EntireRow.Delete _
                    shift:=xlUp
            End With
        End If
    Else
        MsgBox "Es wurden keine Filterkriterien angegeben !"
    End If
    Else
    MsgBox "Es ist kein Autofilter aktiviert !"
    End If
    
    'Herausgefilterte Daten wieder anzeigen lassen
    ActiveSheet.Range("$A$2:$L$1310").AutoFilter Field:=1
    
    Columns("D:D").Select
    Selection.Style = "Comma"
    Columns("K:K").Select
    Selection.Style = "Comma"
    
  
  Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:L2"), Type:=xlFillDefault
    Range("K2:L2").Select
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "Art"
    
    
    
    
End Sub

[p45cal]

Untested, and just at first glance. Instead of:

With ActiveSheet.AutoFilter.Range.Offset(1)
  .Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1).EntireRow.Delete shift:=xlUp
End With

try:

With ActiveSheet.AutoFilter.Range.Offset(1).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1)
  .SpecialCells(xlCellTypeVisible).EntireRow.Delete 'shift:=xlUp no need for this when entire rows are being deleted.
End With

The use of EntireRow might be a little risky if there's data outside the Autofilter range, you could try instead:

  .SpecialCells(xlCellTypeVisible).Delete shift:=xlUp

It might need a tweak if there are hidden columns in the Autofilter range.

[snb]

Basically:

Sub M_snb()
   with sheet1.cells(1).currentregion
      .autofilter 1, "aaa"
      .offset(1).specialcells(12).entirerow.delete
      .autofilter
   end with
End Sub

[DaveG93]

Thanks! it worked!