|
- Sub test()
- Dim arrData
- Dim arrResult
- Dim rowN1 As Long
- Dim rowN2 As Long
- Dim colN As Long
- Dim colNd As Long
- Const colNStart As Long = 6
- Const colNEnd As Long = 37
- Dim blnStart As Boolean
- Dim dateStart As Date
- Dim dateEnd As Date
- Dim WD As Long
- Const maxWD As Long = 7
-
- arrData = Sheet2.Range("A4:AJ" & Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp).Row).Value
-
- ReDim Preserve arrData(1 To UBound(arrData, 1), 1 To UBound(arrData, 2) + 1)
- ReDim arrResult(1 To 7, 1 To 1)
-
- For rowN1 = 2 To UBound(arrData)
- WD = 0
- blnStart = False
-
- For colNd = colNStart To colNEnd
- If Trim(arrData(rowN1, colNd)) <> "" Then
- If Not blnStart Then
- WD = 1
- blnStart = True
- dateStart = arrData(1, colNd)
- Else
- WD = WD + 1
- End If
- Else
- If blnStart Then
- blnStart = False
- If WD >= maxWD Then
- dateEnd = arrData(1, colNd - 1)
- rowN2 = rowN2 + 1
- ReDim Preserve arrResult(1 To 7, 1 To rowN2)
- For colN = 1 To 5
- arrResult(colN, rowN2) = arrData(rowN1, colN)
- Next colN
- arrResult(6, rowN2) = dateStart & "~" & dateEnd
- arrResult(7, rowN2) = WD
- End If
- End If
- WD = 0
- End If
- Next colNd
-
- Next rowN1
-
- ReDim arrData(1 To UBound(arrResult, 2), 1 To UBound(arrResult, 1))
- For rowN1 = 1 To UBound(arrData)
- For colN = 1 To UBound(arrData, 2)
- arrData(rowN1, colN) = arrResult(colN, rowN1)
- Next colN
- Next rowN1
- Sheet1.Range("A3:G" & Sheet1.Rows.Count).ClearContents
- Sheet1.Range("A3").Resize(UBound(arrData, 1), UBound(arrData, 2)) = arrData
- Sheet1.Activate
- End Sub
复制代码 |
评分
-
查看全部评分
|