这是越来越长的一个评论，所以这里的帮助你的方式：我们不想只为别人做东西而不使用OP，甚至不想编写任何代码。要进入VBA，请尝试使用宏阅读器，然后阅读How to avoid using Select in Excel VBA，也许从https://www.simplilearn.com/tutorials/excel-tutorial/excel-vba开始，这样您就可以了解所有东西的用途。至于从初始状态到结束状态的逻辑：

• 声明和设置工作簿和工作表
• Currentrow = 1（或您希望从何处开始）
• 使用for循环通过B列从1到最后一行（For i =当前行到最后一行）
• if Range（“B”& i）. Value 2 =“带薪休假”
• 将i填入另一个变量以存储startRow（如果有多天不工作）
• 相同，但在endrow变量中（这将在以后派上用场）
• 然后使用while循环检查下一行是否也有带薪休假，并相应地调整endrow
• 在同一while循环中，将Currentrow调整为Currentrow + 1（对于每一行，下一行是带薪休假，您需要在for循环中增加步长）
• amtDays = amtDays+1（while循环结束）
• 仍然在If语句中顺便说一句：
• 获取不同工作表的最后一行（lastrow 2），然后在变量的帮助下填写值，例如，Start Date列将得到wbOther.wsOther.Range（“D”& lastrow 2 + 1）.Value2 = wbStart.wsStart.Range（“A”& startRow）.Value2
• amtDays = 1（如果结束）
• 按日期排序（您可以在末尾的另一个工作表中执行此操作）

如果你尝试了所有这些之后仍然被卡住，回来，我们很乐意帮助：）

EDIT在看到您实际尝试了一些东西后，这里是我的修订版本，带有（希望）可以理解的注解，我以前使用过类似的代码，我不知道为什么我选择了for循环而不是while，但在这种情况下，这两种方法都可以工作。

Sub paid_leave()
    Dim wb As Workbook 'declare all your variables upfront and go through your code with F8 from the VBE (VB environment)
    Dim ws As Worksheet, ws2 As Worksheet
    Dim CurrentRow As Long, Lastrow As Long, Lastrow2 As Long, amtDays As Long, i As Long
    
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Blad1") 'a lot easier to write the rest of the code with
    Set ws2 = wb.Sheets("Blad2") 'and it helps when you need the change the name of the sheet/workbook
    CurrentRow = 2
    
    Lastrow = ws.Range("B" & Rows.Count).End(xlUp).Row
    amtDays = 1
    For i = CurrentRow To Lastrow 'could technically work with a while loop since we're not using the i and increment our CurrentRow as well
        If ws.Range("B" & CurrentRow).Value2 = "Paid Leave" Then
            Lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
            ws2.Range("A" & Lastrow2 + 1).Value2 = ws.Range("C" & CurrentRow).Value2 'start and endrow not necessary this way
            ws2.Range("B" & Lastrow2 + 1).Value2 = ws.Range("B" & CurrentRow).Value2
            ws2.Range("D" & Lastrow2 + 1).Value2 = ws.Range("A" & CurrentRow).Value2
            'Do While CurrentRow <> Lastrow 'this is a bad condition, we're already going through all the rows with the for loop, base it on the criteria we want to keep finding aka "Paid Leave"
            Do While ws.Range("B" & CurrentRow + 1).Value2 = "Paid Leave" 'if it's only one day, CurrentRow and amtDays remain on values where it started
                CurrentRow = CurrentRow + 1
                amtDays = amtDays + 1
            Loop
            ws2.Range("C" & Lastrow2 + 1).Value2 = amtDays
            ws2.Range("E" & Lastrow2 + 1).Value2 = ws.Range("A" & CurrentRow).Value2
            amtDays = 1
        End If
        CurrentRow = CurrentRow + 1
        If CurrentRow > Lastrow Then Exit For 'no need to go further (with a while loop this isn't necessary anymore)
    Next i
End Sub

如果你有任何进一步的问题，随时问他们（适当;））

这也可以使用Windows Excel 2010+和Excel 365（Windows或Mac）中提供的Power Query来完成
使用增强查询的步骤

• 在数据表中选择某个单元
• Data => Get&Transform => from Table/Range或from within sheet
• PQ编辑器打开时：Home => Advanced Editor
• 记下第2行中的表名称
• 将下面的M代码粘贴到您看到的位置
• 将第2行中的表名称更改回最初生成的名称。
• 阅读注解并探索Applied Steps以了解算法

let

//Change Name in next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],

//Set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Status", type text}, {"Name", type text}}),

//Group by status and Name with "GroupKind.Local" argument
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Status", "Name"}, {
        {"Days", each Table.RowCount(_), Int64.Type}, 
        {"Start Date", each List.Min([Date]), type nullable date}, 
        {"End Date", each List.Max([Date]), type nullable date}},
        GroupKind.Local),

//Select only "Paid Leave" in the Status column
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Status] = "Paid Leave"))
in
    #"Filtered Rows"

• 注意：大部分操作都可以在用户界面中完成。但是，需要将Date列的默认type从datetime更改为date，并且需要手动添加GroupKind.Local参数 *

在Notus_Panda给出最终答案之前，我已经做了一些修改，并添加了另一种方法。再次感谢。

Sub paid_leave()
Dim wb As Workbook
Dim sh, sh2 As Worksheet

Set wb = ActiveWorkbook
Set sh = wb.Sheets("Sheet1")
Set sh2 = wb.Sheets("Sheet2")

Currentrow = 2
i = 2
lastrow = wb.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
amtDays = 1

Do While i <= lastrow
    If wb.Sheets("Sheet1").Range("B" & i).Value2 = "Paid Leave" Then
        startRow = i
        endrow = i
        Do While wb.Sheets("Sheet1").Range("B" & i + 1).Value2 = "Paid Leave"
                endrow = endrow + 1
                amtDays = amtDays + 1
                i = i + 1
        Loop
        lastrow2 = wb.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
        wb.Sheets("Sheet2").Range("A" & lastrow2 + 1).Value2 = wb.Sheets("Sheet1").Range("C" & startRow).Value2
        wb.Sheets("Sheet2").Range("B" & lastrow2 + 1).Value2 = wb.Sheets("Sheet1").Range("B" & startRow).Value2
        wb.Sheets("Sheet2").Range("C" & lastrow2 + 1).Value2 = amtDays
        wb.Sheets("Sheet2").Range("D" & lastrow2 + 1).Value2 = wb.Sheets("Sheet1").Range("A" & startRow).Value2
        wb.Sheets("Sheet2").Range("E" & lastrow2 + 1).Value2 = wb.Sheets("Sheet1").Range("A" & endrow).Value2
        amtDays = 1
        i = i + 1
    End If
    i = i + 1
Loop
End Sub