i want to delete an specific number of rows after i filtered using autofilter im using the following code
The main issue is that after sorting the data on the other worksheet the code erase all the filtered rows visible, but i want to know if exist a way to delete just a number of rows after filter, for example if i want to erase only 5 rows and after filter i have 10 rows it should delete only five, not all.
Private Sub Worksheet_Change(ByVal Target As Range) Dim aLot As Long Dim rollsNumber As Long If Target.Column <> 6 Then Exit Sub If Target.Count > 1 Then Exit Sub 'i have a worksheet with values so i assign them to the next variables aLot = Target.Cells(1, 2).Value rollsNumber = Target.Cells(1, 3).Value 'in case of cell that change is "on going" If Target.Value = "on going" Then Dim ws As Worksheet 'here i change of worksheet because i want to delete rows of that worksheet Set ws = ThisWorkbook.Worksheets("Movements") ws.Activate 'Clear any existing filters On Error Resume Next ws.ShowAllData On Error GoTo '1. Sorting by the number of lot ws.Rnge("A1:G1000").AutoFilter Field:=1, Criteria1:=aLot '2. Delete Rows Application.DisplayAlerts = False ws.Range("A2:G1000").SpecialCells(xlCellTypeVisible).Delete Application.DisplayAlerts = True '3. Clear Filter On Error Resume Next ws.ShowAllData On Error GoTo 0 End If End Sub
Can someone help me?