Dragging data over without overwriting existing data.

DannyBidz

New Member
Joined
May 7, 2014
Messages
19
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.

AB
Colour
Blue
Red
Green
Yellow
Brown
Purple
Silver
Orange
Pink

<tbody>
</tbody>

Thanks for the help!

Cheers,

Dan.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
Will col A always be blank if there is a value in col B?
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
If the blanks and the value always line up like in your example this should work.



Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
If the answer to post#2 is yes, another option is
Code:
Sub DannyBidz()
   Range("A:A").SpecialCells(xlBlanks).Delete xlToLeft
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
If Blanks in Col A & values in col B won't always line up, try
Code:
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

New Member
Joined
May 7, 2014
Messages
19
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,917
Messages
5,525,621
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top