合并拆分记录的Excel行

Question

我正在导出一个报告，在导入到Excel时，记录被拆分为多个行。导入后，每个报告的行数超过30K。唯一不被分割的列是列A，每一列都可以被分割成2-4行。有没有办法自动合并这些行，使其看起来像附加的图像的底部？

​

Answer 1

您可能想要使用以下代码：

Option Explicit

Sub main()
    Dim area As Range
    Dim strng As String
    Dim iRow As Long, startRow As Long, endRow As Long, iCol As Long

    With Worksheets("Export") '<--| change "Export" with your actual sheet name
        With .Range("A2:A" & .UsedRange.Rows(.UsedRange.Rows.Count).row).SpecialCells(xlCellTypeBlanks)
            For Each area In .Areas
                strng = ""
                startRow = area.End(xlUp).row
                endRow = area.Rows(area.Rows.Count).row
                iCol = area.End(xlToRight).Column
                For iRow = startRow To endRow
                    strng = strng & .Parent.Cells(iRow, iCol) & " "
                Next iRow
                .Parent.Cells(startRow, iCol) = strng
            Next area
            .EntireRow.Delete
        End With
    End With
End Sub