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:

	A	B	C	D	E	F
1	john	group1	xxx	group3	group4	group5
2	kelly	group1	xxx	xxx	group4	xxx
3	chris	group1	group2	xxx	xxx	group5
4	teri	group1	xxx	group3	xxx	group5
5	sam	xxx	group2	xxx	xxx	group5

<tbody>
</tbody>

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

Ideally I want the data to look like this:

	A	B	C	D	E	F
1	john	group1	group3	group4	group5
2	kelly	group1	group4
3	chris	group1	group2	group5
4	teri	group1	group3	group5
5	sam	group2	group5

<tbody>
</tbody>

 

[Vbagreenhorn1]

try this:

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