Option Explicit
Public Sub Test()
Dim loZeile As Long, loSpalte As Long
Dim raBereich As Range, raBereich1 As Range, raZelle As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In ThisWorkbook.Worksheets
With ws.UsedRange
loZeile = .Rows.Count
loSpalte = .Columns.Count
Set raBereich = .Range(.Cells(2, 1), .Cells(loZeile, loSpalte))
For Each raZelle In raBereich
If raZelle.MergeCells = True Then
raZelle.EntireRow.Delete
End If
Next raZelle
Set raBereich1 = .Range(.Cells(1, 5), .Cells(loZeile, 5))
raBereich1.FormulaLocal = "=WENNFEHLER(SVERWEIS(A1;A:B;2;Falsch);"""")"
raBereich1.Value = raBereich1.Value
.Range("C:D").EntireColumn.Delete
End With
Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Set raBereich = Nothing
Set raBereich1 = Nothing
End Sub