Identify Blank Cells in Col & move coresponding data
 

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

Identify Blank Cells in Col & move coresponding data
 

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Yogi_Bear_79" wrote in message
...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004

Identify Blank Cells in Col & move coresponding data
 

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.



"Ken Wright" wrote in message
...
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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Yogi_Bear_79" wrote in message
...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004

Identify Blank Cells in Col & move coresponding data
 

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

--
Regards,
Tom Ogilvy

"Yogi_Bear_79" wrote in message
...
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.



"Ken Wright" wrote in message
...
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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--



"Yogi_Bear_79" wrote in message
...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004

Identify Blank Cells in Col & move coresponding data
 

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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Yogi_Bear_79" wrote in message
...
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.



"Ken Wright" wrote in message
...
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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03



--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)


--------------------------------------------------------------------------
--



"Yogi_Bear_79" wrote in message
...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004

Identify Blank Cells in Col & move coresponding data
 

Yes.

--
Regards,
Tom Ogilvy

"Yogi_Bear_79" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Yogi_Bear_79" wrote in message
...
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.



"Ken Wright" wrote in message
...
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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03




--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)



--------------------------------------------------------------------------
--



"Yogi_Bear_79" wrote in message
...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004