vbaexceldeduplication

VBA code to remove random blank cells from a sheet


What would be the VBA code to remove blank cells randomly placed in a spreadsheet. Input

ColA   ColB   ColC   ColD   ColE
 A             B             D
 H      J             I
 F             B             O

Output Should be like:

ColA   ColB   ColC   ColD   ColE
 A      B      D
 H      J      I
 F      B      O

Solution

  • This solution is very fast and is free from the three caveats listed in my comment below the OP question:

    Public Sub CullValues()
        Dim i&, j&, k&, v
        v = ActiveSheet.UsedRange
        For i = 1 To UBound(v, 1)
            k = 0
            For j = 1 To UBound(v, 2)
                If Len(v(i, j)) Then
                    k = k + 1
                    v(i, k) = v(i, j)
                    If j > k Then v(i, j) = Empty
                End If
            Next
        Next
        [a1].Resize(UBound(v, 1), UBound(v, 2)) = v
    End Sub