如何将随机数的单元格与列中的空白单元格合并？

Example of my dataset:

我的数据集示例:

blank  
1  
2  
blank  
3  
4  
5  
blank   
6

I want to merge all cells below a blank cell into the blank cell, but stop counting when it reaches the next blank cell.

我想将空白单元格下方的所有单元格合并到空白单元格中,但是当它到达下一个空白单元格时停止计数。

End result should look like this, with the strings concatenated

最终结果应如下所示,并将字符串连接起来

12  
345  
6  

I'm currently trying to create an array with 1s and 2s with 2 meaning its a blank cell, then counting the 1s and merging them. I don't know if this will work or if there is an easier way to do this.

我正在尝试创建一个带有1和2的数组,其中2表示一个空白单元格,然后计算1并合并它们。我不知道这是否有效,或者是否有更简单的方法可以做到这一点。

4 个解决方案

#1

This requires you to select the area you want to merge, starting with the first blank cell and ending with the last cell with a value. It will delete entire rows; not sure if that's what you wanted:

这要求您选择要合并的区域,从第一个空白单元格开始,最后一个单元格以值结束。它会删除整行;不确定这是不是你想要的:

Sub MergeConstantsIntoEmpties()
Dim BlankCells As Excel.Range
Dim ConstantCells As Excel.Range
Dim i As Long
Dim MungedContents As String

With Selection
    Set BlankCells = .SpecialCells(xlCellTypeBlanks)
    Set ConstantCells = .SpecialCells(xlCellTypeConstants)
End With

For i = 1 To BlankCells.Areas.Count
    If ConstantCells.Areas(i).Count = 1 Then
        MungedContents = ConstantCells.Areas(i).Value
    Else
        MungedContents = Join(Application.WorksheetFunction.Transpose(ConstantCells.Areas(i).Value))
    End If
    BlankCells.Areas(i).Value = MungedContents
Next i
ConstantCells.EntireRow.Delete
End Sub

#2

If we start with:

如果我们开始:

and run this macro:

并运行此宏:

Sub PileOn()
Dim N As Long, st As String
Dim i As Long, v As Variant
N = Cells(Rows.Count, "A").End(xlUp).Row

For i = N To 1 Step -1
   v = Cells(i, 1).Value
   If v <> "" Then
      st = st & v
      Cells(i, 1).Delete shift:=xlUp
   Else
      Cells(i, 1).Value = st
      st = ""
   End If
Next i
End Sub

We end up with:

我们最终得到:

EDIT#1:

To fix the order of the concatenated cells use this instead:

要修复连接单元格的顺序,请使用以下代码:

Sub PileOn()
Dim N As Long, st As String
Dim i As Long, v As Variant
N = Cells(Rows.Count, "A").End(xlUp).Row

For i = N To 1 Step -1
   v = Cells(i, 1).Value
   If v <> "" Then
      st = v & st
      Cells(i, 1).Delete shift:=xlUp
   Else
      Cells(i, 1).Value = st
      st = ""
   End If
Next i
End Sub

#3

Here is my take on it.

这是我的看法。

Sub JoinBetweenTheLines()
Dim X As Long
X = 1
Do Until X >= Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & X).text = "" Then
        Range("A" & X).Delete xlUp
    ElseIf Range("A" & X).Offset(1, 0).text = "" Then
        X = X + 1
    Else
        Range("A" & X).Formula = Join(Application.Transpose(Range("A" & X & ":A" & X + 1)), "")
        Range("A" & X + 1).Delete xlUp
    End If
Loop
End Sub

I normally work backwards also but for this one went forwards.

我通常也会向后工作但是因为这个人向前走了。

#4

I had memory processing in mind.

我记住了内存处理。

Sub merg()
    Dim v As Long, w As Long, vVALs As Variant
    With ActiveSheet    'reference the worksheet properly!
        With .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
            vVALs = .Cells.Value2
            For v = LBound(vVALs, 1) To UBound(vVALs, 1)
                If vVALs(v, 1) = vbNullString Then
                    For w = v + 1 To UBound(vVALs, 1)
                        If vVALs(w, 1) = vbNullString Then Exit For
                        vVALs(v, 1) = vVALs(v, 1) & vVALs(w, 1)
                        vVALs(w, 1) = vbNullString
                    Next w
                End If
            Next v
            .Cells = vVALs
            With .SpecialCells(xlCellTypeBlanks)
                .Delete Shift:=xlUp
            End With
        End With
    End With
End Sub