excel - Excel: How to move text from multiple cells to first cell?

Question

i have a hug excel table:

I need a formula to move data to the first cell, like below.

please help me to do that.

标签： excelexcel-formulaexcel-2010

Answer 1

这行得通。只要确保将 source_rng 设置为您想要的范围即可。也应该很好地扩大规模。编辑：现在意识到这不会保留颜色，可以解决这个问题

Sub main()
Dim ws                              As Worksheet
Dim source_rng                      As Range
Dim target_rng                      As Range
Dim source_data()                   As Variant
Dim clean_data()                    As Variant
Dim column_count                    As Long
Dim row_count                       As Long
Dim i                               As Long
Dim j                               As Long
Dim k                               As Long

    Set ws = ActiveSheet

    Set source_rng = ws.Range("A1:F19200")

    column_count = source_rng.Columns.Count
    row_count = source_rng.Rows.Count

    ReDim source_data(1 To row_count, 1 To column_count)
    ReDim clean_data(1 To row_count * column_count, 1)
    source_data = source_rng

    k = 1
    For j = 1 To row_count
        For i = 1 To column_count
            If source_data(j, i) <> "" Then
                k = k + 1
            End If
        Next i
    Next j

    ReDim clean_data(1 To k, 1)

    k = 1
    For j = 1 To row_count
        For i = 1 To column_count
            If source_data(j, i) <> "" Then
                clean_data(k, 0) = source_data(j, i)
                k = k + 1
            End If
        Next i
    Next j

    source_rng.Clear
    Set target_range = ThisWorkbook.ActiveSheet.Range("A1:A" & UBound(clean_data))
    target_range.value = clean_data
End Sub