Identify Blank Cells in Col & move coresponding data

  • Thread starter Thread starter Yogi_Bear_79
  • Start date Start date
Y

Yogi_Bear_79

I need to identify which rows have a blank cell in colum E. I then need to
move the data from:

F to E
G to F
H to G

I would like to do this without moving the data from the remaing rows.

Manually I would filter by blank for that column, then delete the affected
cells from column E. I would allow Excel to move the data to the left. I
would then go to column H and insert blank cells, moving the remaining rows
I~?? to the right
 
Assuming nothing in the Cols past Column H, select the entire range in Col E
that you want to fix, do Edit / Go To / Special / Blanks. Now do Edit / Delete
/ Shift cells left.
 
Ken,

That's a pretty cool way of doing that. But I do have data past col H, that
needs to be shifted back. Also this would be preferable via VB. I realize I
can record the macro, but since these cells can differ, I didn't want to
limit myself to certain rows.
 
Sub AATester1()
Dim rng As Range, rng1 As Range
On Error Resume Next
Set rng = Columns(5).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
Set rng1 = Intersect(Columns(1), rng.EntireRow)
rng.Delete Shift:=xlToLeft
Set rng1 = Intersect(Columns(8), rng1.EntireRow)
rng1.Insert Shift:=xlToRight
End If
End Sub
 
Cool,

Tom,

Does this work because once the special cells (blanks are selected it
only works with those rows versus the entire column?
 
Yes.

--
Regards,
Tom Ogilvy

Yogi_Bear_79 said:
Cool,

Tom,

Does this work because once the special cells (blanks are selected it
only works with those rows versus the entire column?


Edit
Click to expand...
 
Back
Top