Help copying a range with spaces to a range without spaces

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi, I'm trying to find the simplest solution (well, any solution) to the
following problem:

1 2 3 4 5 6
A F
B F Y S
C
D G
E

I need to list the above as:

F
F
G
Y
S

In other words listing all the contents of Column 1 from Rows A:E, in the
order they appear, ignoring spaces, followed by the contents of Column 2
etc. Up to Column whatever. There may be duplicates which I want to keep,
except for the spaces.

I can do it in my head, but I have no idea how to code it.

Any ideas?

Andy
 
This should work:

Sub Tester()
For i = 1 To 5
For j = 1 To 256
sTest = Cells(i, j)
If sTest <> Empty Then
LastCell = Cells(65536, 1).End(xlUp).Row
If LastCell < 5 Then LastCell = 5
Cells(LastCell + 1, 1) = sTest
End If
Next j
Next i
Rows("1:5").Delete
End Sub

Regards,
Shockley
 
Back
Top