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 :D




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!