Removing Blanks from an Array

[pennywise117]

How do you remove blanks from an array? I want to summarize data in Columns to eventually transpose the data into rows, but can't figure out how to remove the blanks in a quick/non-manual way.

The data is configured similar to the following -- with blank cells being noted with XXX:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]john[/TD]
[TD]group1[/TD]
[TD]xxx[/TD]
[TD]group3[/TD]
[TD]group4[/TD]
[TD]group5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]kelly[/TD]
[TD]group1[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]group4[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]chris[/TD]
[TD]group1[/TD]
[TD]group2[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]group5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]teri[/TD]
[TD]group1[/TD]
[TD]xxx[/TD]
[TD]group3[/TD]
[TD]xxx[/TD]
[TD]group5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]sam[/TD]
[TD]xxx[/TD]
[TD]group2[/TD]
[TD]xxx[/TD]
[TD]xxx[/TD]
[TD]group5[/TD]
[/TR]
</tbody>[/TABLE]

My total Range = A:BX
and Rows = 5:331

Ideally I want the data to look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]john[/TD]
[TD]group1[/TD]
[TD]group3[/TD]
[TD]group4[/TD]
[TD]group5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]kelly[/TD]
[TD]group1[/TD]
[TD]group4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]chris[/TD]
[TD]group1[/TD]
[TD]group2[/TD]
[TD]group5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]teri[/TD]
[TD]group1[/TD]
[TD]group3[/TD]
[TD]group5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]sam[/TD]
[TD]group2[/TD]
[TD]group5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Vbagreenhorn1]

try this:

Sub Pennywise()
Cells(1).CurrentRegion.SpecialCells(4).Delete xlShiftToLeft
End Sub