Teste mal...
23.11.2017 11:24:02
Michael
folgenden Code, Christian,
...dem allerdings die Annahme/Bedingung zu Grunde liegt, dass die Start- und End-Zellen strikt paarweise auftreten, es also nicht mehr Start- als Ende-Zellen gibt oder vice versa.
Sub a()
Dim Wb As Workbook: Set Wb = ThisWorkbook
Dim Ws As Worksheet: Set Ws = Wb.Worksheets("Tabelle1")
Dim r As Range, f As Range, ff$, a(), b(), i&
Application.ScreenUpdating = False
With Ws
Set r = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
Set f = r.Find(what:="1", LookIn:=xlValues, lookat:=xlWhole)
ReDim a(1 To r.Cells.Count)
If f Is Nothing Then
MsgBox "Kein Starteintrag gefunden!", vbCritical, "Abbruch"
Exit Sub
Else:
ff = f.Address
Do
i = i + 1
a(i) = f.Address
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address ff
ReDim Preserve a(1 To i): i = 0
Set f = Nothing: Set r = Nothing
End If
Set r = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
Set f = r.Find(what:="1", LookIn:=xlValues, lookat:=xlWhole)
ReDim b(1 To r.Cells.Count)
If f Is Nothing Then
MsgBox "Kein Endeintrag gefunden!", vbCritical, "Abbruch"
Exit Sub
Else:
ff = f.Address
Do
i = i + 1
b(i) = f.Address
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address ff
ReDim Preserve b(1 To i): i = 0
Set f = Nothing: Set r = Nothing
End If
For i = LBound(a) To UBound(a)
Set r = .Range(.Range(a(i)), .Range(b(i)))
r.Offset(, 2).Resize(r.Rows.Count, 1).Value = 1
Next i
End With
Set Wb = Nothing: Set Ws = Nothing: Set r = Nothing
Erase a: Erase b
End Sub
LG
Michael