You can always use Range("A:A").ClearContents heres an example
# Split long col into 7 equal col
Sub SplitIntoCellsPerColumn()
Range("B2:H1894").ClearContents
Dim X As Long, LastRow As Long, vArrIn As Variant, vArrOut As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
numofrows = LastRow / 7
numofrows_rundup = Round_Up(numofrows)
vArrIn = Range("A1:A" & LastRow)
ReDim vArrOut(1 To numofrows_rundup, 1 To Int(LastRow / numofrows_rundup) + 1)
For X = 0 To LastRow - 1
vArrOut(1 + (X Mod numofrows_rundup), 1 + Int(X / numofrows_rundup)) = vArrIn(X + 1, 1)
Next
Range("B2").Resize(numofrows_rundup, UBound(vArrOut, 2)) = vArrOut
Range("A:A").ClearContents
End Sub
Function Round_Up(ByVal d As Double) As Integer
Dim result As Integer
result = Math.Round(d)
If result >= d Then
Round_Up = result
Else
Round_Up = result + 1
End If
End Function
What you're doing should work (see Delete a table or clear its contents), and I can't reproduce the resulting behavior that you're describing.
– G-Man Says 'Reinstate Monica' – 2015-03-07T02:06:20.797@John, Just like G-Man I can't reproduce the problem either. Backspace will delete all the contents along with the cells but the delete key will just remove the contents only. – Adam – 2015-03-07T07:15:11.180