Copy data from multiple columns to one single row using .selection

[themick]

​	A​	B​	C​	D​	E​	F​
1​	John Doe	​	​	​	​	​
2​	​	​	​	​
3​	​	​	Anytown	​	​	​
4​	​	​	​	CA​	91001​	​
5​	​	​	​	​	​	555.555.1212
6​	​	​	​	​	​	​
7​	​	111 North	​	​	​	​
8​	Jill Doe	​	​	TX​	​	​
9​	​	​	​	​
10​	​	​	​	​	81001​	555.555.2424

<tbody>
[TD="colspan: 2"] 235 west street [/TD]

[TD="colspan: 2"] SomeTown [/TD]

</tbody>

Company is working on how the data is exported but for now, this is how it is received.
In the example, John Doe is Cell A1 with the last field of his record being F5, the phone #.
The second record, starts in B7, '111 North', but would include A8, Jill Doe, as part of that record, again ending in Col F (F10)

I need to be able to select the data the way it is received, could look like either one of these records or could get lucky and all of the data may actually be in one row.
I then need to copy the selected data from these Rows/Columns to the next available row in Worksheet(2) so when completed, should look like this:

John Doe	235 west street	Anytown	CA	91001​	555.555.1212
Jill Doe	111 North	SomeTown	TX	81001​	555.555.2424

<tbody>
</tbody>

Again-the data comes in this way, varying from record to record so there is no way to set a range other than to select the rows/fields that would be included in the record. I have seen examples of this on this site but not any using selected criteria. if there is, can someone point that to me and if not - can someone look at this and let me know if possible - how would to write the code to perform this task? Thank you in advance

 

[Fluff]

If your blanks are empty try

Sub DelBlanks()
Range("A:F").SpecialCells(xlBlanks).Delete
End Sub

This will simply delete the blank cells shifting everything up

 

[themick]

If your blanks are empty try

Sub DelBlanks()
Range("A:F").SpecialCells(xlBlanks).Delete
End Sub

This will simply delete the blank cells shifting everything up

That would do it, thank you!

M

 

[Fluff]

You're welcome