Hello again!
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.
Code
- 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?
