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
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