Moving data from Col B to Col A

[Michael Koerner]

I need to move the data from Col B to Col A only if the cell in Col A is empty.
Is there an easy way to do this?

 

[Markus Scheible]

Hi Michael,

try:

For i = 1 To 65532

If Range("a" & i).Value = "" Then Range("a" & i).Value =
Range("b" & i)
End If

Next i

Best

Markus

 

[Tom Ogilvy]

or, if the cells are really empty/blank, a quicker approach would be

Sub ABCD()
dim cell as Range
On Error Resume Next
for each cell in Activesheet.columns(1).Specialcells(xlBlanks)
cell.Value = Cell.offset(0,1).Value
' uncomment the next line if you want
' the value in B cleared
'cell.Offset(0,1).ClearContents
Next
if err <> 0 then
msgbox "No Blank Cells
err.clear
End if
End Sub

 

[Michael Koerner]

Tom I tried your macro, and all I got was the message "No blank Cells

--

Regards
Michael Koerner


or, if the cells are really empty/blank, a quicker approach would be

Sub ABCD()
dim cell as Range
On Error Resume Next
for each cell in Activesheet.columns(1).Specialcells(xlBlanks)
cell.Value = Cell.offset(0,1).Value
' uncomment the next line if you want
' the value in B cleared
'cell.Offset(0,1).ClearContents
Next
if err <> 0 then
msgbox "No Blank Cells
err.clear
End if
End Sub

 

[Tom Ogilvy]

That means they must not actually be blank. Do you have formulas in them
(not blank). Or did you clear them by hitting the spacebar (not blank).

I ran it on a sheet that actually had blank cells in column A and it worked
perfectly.

Guess you will have to clean up your data or go with Markus's code.

 

[Michael Koerner]

Your absolutely correct. Once I cleared the contents of the supposedly blank
cells your macro worked as advertised. Which is good, as I wouldn't know how to
use Markus's code <g>

--

Regards
Michael Koerner


That means they must not actually be blank. Do you have formulas in them
(not blank). Or did you clear them by hitting the spacebar (not blank).

I ran it on a sheet that actually had blank cells in column A and it worked
perfectly.

Guess you will have to clean up your data or go with Markus's code.