|
本帖最后由 adders 于 2013-7-6 09:54 编辑
个人建议,类似这种表格,能不用合并单元格就不要用,EXCEL和VBA对合并单元格的操作使用上有诸多的不便.
当然就按照你现有的表格及要求,也不难做到,以下是代码,代码后是附件(打开文件后点"Test"按钮就可以测试) - Sub test()
- Const FRow = 3
- Dim LRow As Long, n As Long
- Dim sBZ As String
- LRow = Cells(Rows.Count, "M").End(xlUp).Row
- Application.ScreenUpdating = False
- Range(Cells(FRow, "Q"), Cells(LRow, "Q")).ClearContents
- i = FRow
- Do While i <= LRow
- If Cells(i, "Q").MergeCells Then n = Cells(i, "Q").MergeArea.Count Else n = 1
- For j = 4 To 12
- If Len(Trim(Cells(i, j))) + Len(Trim(Cells(i + n - 1, j))) > 0 Then
- If sBZ = "" Then
- sBZ = Cells(FRow - 1, j).Value
- Else
- sBZ = sBZ & "&" & Cells(FRow - 1, j).Value
- End If
- Cells(i, "Q") = sBZ
- End If
- Next i
- sBZ = ""
- i = i + n
- Loop
- Application.ScreenUpdating = True
- End Sub
复制代码- Sub test2() '如有超过2行合并的情况,请用以下代码. 对应附后文件: "合并单元格休假.xls"
- Const FRow as Byte = 3
- Dim LRow As Long, n As Long, i As Long, j As Long
- Dim sBZ As String
- LRow = Cells(Rows.Count, "M").End(xlUp).Row
- Application.ScreenUpdating = False
- Range(Cells(FRow, "Q"), Cells(LRow, "Q")).ClearContents
- i = FRow
- Do While i <= LRow
- If Cells(i, "Q").MergeCells Then n = Cells(i, "Q").MergeArea.Count Else n = 1
- For j = 4 To 12
- If WorksheetFunction.CountA(Range(Cells(i, j), Cells(i + n - 1, j))) > 0 Then
- If sBZ = "" Then
- sBZ = Cells(FRow - 1, j).Value
- Else
- sBZ = sBZ & "&" & Cells(FRow - 1, j).Value
- End If
- Cells(i, "Q") = sBZ
- End If
- Next j
- sBZ = ""
- i = i + n
- Loop
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|