Moving data to blank column??????

  • Thread starter Thread starter Malcolm
  • Start date Start date
M

Malcolm

Hi

I have a worksheet with various columns of data.

Some of the information takes up 5 columns and some only
takes up 4 columns. What I would like to do is run a
macro to move data from the cells in column 4 into column
5 where column 5 is blank.

Could I have some help on this please.

Many thanks

Malcolm Davidson
 
Sub Malcolms_blank_cells()
Range ("e2").select
do until activecell.offset(0,-1).value = ""
If activecell.value="" then
activecell.value=activecell.offset(0,-1).value
end if
selection.offset(1,0).select
Loop
end sub

Steve
 
You can accomplish this task without using a macro.

Assuming your data is in columns A-E.
Use the following the formula in F1.
=IF(LEN(E1)=0,"",D1)
Use the following the formula in G1.
=IF(LEN(E1)=0,D1,E1)
Copy down these formulae and you should see the properly
oriented data in columns F & G.
Hardcode columns F & G (that is, Copy/PasteSpecialValues).
You then can delete columns D & E and the data should be
properly oriented.

Below is a macro solution.
(I suggest you backup your data first before running this
macro so you can see the "before" version.)
Selected the data range for your column 5.
Then run the following macro.

Sub ShiftData()
For irow = ActiveCell.Row To Selection.Rows.Count
If Len(Cells(irow, ActiveCell.Column)) = 0 Then
Cells(irow, ActiveCell.Column) = Cells(irow,
ActiveCell.Column - 1)
Cells(irow, ActiveCell.Column - 1).Clear
End If
Next irow
End Sub
 
Select Col 4 as a whole and insert a new column, so that your data is now in columns 5 and 6 with
4 being empty. Select all of Col 6 and do Edit / Go To / Special / Blank Cells, then do Edit /
Delete / Move cells left.

Assumes you have no data of any consequence past Col 6.
 
Back
Top