Paste data into a column without overriding data in that column

[jjexcel28]

I'd like to paste data from one column into the blank rows of another column; the second column does have some data in it but I don't want to overide that data when I paste into that column.

So I want the data from that I paste from the first column to paste into the blank cells in the second column in succession (regardless if the second column has preocuppied cells. Here's a visual example.

http://www.xyz000.info/projects/excel-example.gif

 

[crimson_b1ade]

hold alt+f11, insert a new module (Insert\Module on menu bar), paste the below code, close the vba ide.

hold alt+f8, select the below macro named "FaroShuffle"

Sub FaroShuffle()
 
Application.ScreenUpdating = False
 
Dim LastRow As Long, i As Long, k As Long
 
LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
k = 5
 
For i = 5 To LastRow
 
    Do Until Range("E" & k) = ""
 
        k = k + 1
 
    Loop
 
Range("E" & k) = Range("A" & i)
 
Next
 
Application.ScreenUpdating = True
 
End Sub

 

[Peter_SSs]

jjexcel28

Welcome to the MrExcel board!

Assuming ...

1. Your sample with spokane in row 15 was a mistake (should have been in row 16?)

2. E5 and E6 already contain data.

... then maybe this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyToBlanks()<br>    <SPAN style="color:#00007F">Dim</SPAN> ARange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> ARange = Range("A5", Range("A" & Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> ARange<br>        Range("E5").End(xlDown).Offset(1).Value = c.Value<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jjexcel28]

Wow awesome kudos to you both --- one other thing - when running the macro, can blank fields in column A count as data? so if the column was

atlanta

detroit

spokane

instead of

atlanta
detroit
spokane

Can the blank cells be pasted into column E as well?

 

[jjexcel28]

OK please ignore the last post -- this is what needs to be done:

http://www.xyz000.info/projects/cell-example2.gif

Basically blank cells need to be able to be included in pasting into the second column from the first one. Also, there is an extra space beneath each set of data in the second column!

Thanks again!

 

[Peter_SSs]

This is quite different to your original question where you wanted the blank cells in the destination column filled. Now it appears you only want every second blank cell in the destination column filled. For example, 'Atlanta' (from A5) goes into the first blank cell (row 8). Then a blank (from A6) goes into the blank in row 13. But nothing goes into the blank in row 9?

Anyway, for the layout you have provided, this might do it:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyToBlanks()<br>    <SPAN style="color:#00007F">Dim</SPAN> ARange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, StartCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> ARange = Range("A5", Range("A" & Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">Set</SPAN> StartCell = Range("E5")<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> ARange<br>        StartCell.End(xlDown).Offset(1).Value = c.Value<br>        <SPAN style="color:#00007F">Set</SPAN> StartCell = StartCell.End(xlDown).<SPAN style="color:#00007F">End</SPAN>(xlDown)<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>