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

themick

New Member
Joined
May 26, 2018
Messages
48

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
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
If your blanks are empty try
Code:
Sub DelBlanks()
Range("A:F").SpecialCells(xlBlanks).Delete
End Sub
This will simply delete the blank cells shifting everything up
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top