Sub Verwijderen_BewPlaatsen()
Dim lNoOfRecords As Double
Dim lNoToErase
Dim FoundCell As Range
Dim Criteria As String
'Aantal rijen vinden op blad "Uren"
lNoOfRecords = Application.CountIf(Sheets("Uren").Columns(1), "<>") + 1
'Aantal te verwijderen bewerkingsplaatsen
lNoToErase = Application.CountIf(Sheets("Aanpassen").Columns(7), "<>")
'Gespecificeerde bewerkingsplaatsen verwijderen uit tabblad "Uren"
For i = 2 To lNoToErase
Criteria = Sheets("Aanpassen").Cells(i, 7)
Sheets("Uren").Select
Sheets("Uren").Range(Cells(2, 1), Cells(lNoOfRecords, 21)).AutoFilter Field:=7, Criteria1:=Criteria
Set FoundCell = Sheets("Uren").Range(Cells(2, 1), Cells(lNoOfRecords, 21)).Find(What:=Criteria)
If Not FoundCell Is Nothing Then
Rows(FoundCell.Row & ":" & FoundCell.Row).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("uren").Range(Cells(2, 1), Cells(lNoOfRecords, 21)).AutoFilter Field:=7
MsgBox (FoundCell.Row)
Else
MsgBox ("Machine nr " & Criteria & " is not found")
Sheets("Uren").Range(Cells(2, 1), Cells(lNoOfRecords, 21)).AutoFilter Field:=7
End If
Next i
'Tabblad "Uren" sorteren op kolom A
ActiveWorkbook.Worksheets("Uren").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Uren").AutoFilter.Sort.SortFields.Add2 Key:=Range("A2:A50332"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Uren").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Nieuw aantal rijen vinden
lNoOfRecords = Application.CountIf(Sheets("Uren").Columns(1), "<>") + 1
'Volgorde nummers (kolom A) opnieuw doornummeren
Range(Cells(3, 1), Cells(4, 1)).Select
Selection.AutoFill Destination:=Range(Cells(3, 1), Cells(lNoOfRecords, 1))
End Sub