Dragging data over without overwriting existing data.

[DannyBidz]

Hi All,

Please see the below example. Is there a way I can select the data in col. B and either cut / paste or drag over into col. A without overwriting the existing data in Col. A? So as to essentially just fill the gaps.

Dragging the Col. B items over one at a time will be rather time consuming for the list I have on my actual sheet.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Colour[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Brown[/TD]
[/TR]
[TR]
[TD]Purple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Silver[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Pink[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the help!

Cheers,

Dan.

 

[Fluff]

Will col A always be blank if there is a value in col B?

 

[Scott T]

If the blanks and the value always line up like in your example this should work.

Sub fillblank()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For x = 2 To lr
    If Cells(x, "A") = "" Then Cells(x, "A") = Cells(x, "B")
Next x
Range("B2:B" & lr).ClearContents

End Sub

 

[Fluff]

If the answer to post#2 is yes, another option is

Sub DannyBidz()
   Range("A:A").SpecialCells(xlBlanks).Delete xlToLeft
End Sub

 

[Fluff]

If Blanks in Col A & values in col B won't always line up, try

Sub DannyBidz2()
   With Range("B:B").SpecialCells(xlConstants)
      .Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
      Range("A:A").SpecialCells(xlBlanks).Delete xlUp
      .ClearContents
   End With
End Sub

 

[DannyBidz]

Hi folks, sorry it's took a while respond, and yes the values in col A will always be blank when there is a value in col B, so I shall try the options suggested.

Thank you so much for the help