Identify Blank Cells in Col & move coresponding data

[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

 

[Ken Wright]

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.

 

[Yogi_Bear_79]

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.

 

[Tom Ogilvy]

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

 

[Yogi_Bear_79]

Cool,

Tom,

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

 

[Tom Ogilvy]

Yes.

--
Regards,
Tom Ogilvy

Cool,

Tom,

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


Edit