I should mention that this code requires each region to have Column I
completely filled in (that is, no blank cells in Column I in each region).
That means you can't run the macro again until all "holes" in Column I's
data are filled back in. If you will need to run the code again before you
can fill all "holes" in Column I's data, then you need to tell me a column
that will always have *each* row of *each* region completely filled in and I
will change the code to use it as the determiner of where each region
starts.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Okay, try this code instead...

Sub ManipulateColumnI()
Dim X As Long
Dim FirstICell As Range
Dim RegionStartRows() As Long
Const StartOfDataRow As Long = 2
With Worksheets("Sheet6")
Set FirstICell = .Cells(StartOfDataRow, "I")
ReDim RegionStartRows(1 To 1)
RegionStartRows(1) = StartOfDataRow
Do
Set FirstICell = FirstICell.End(xlDown).End(xlDown)
If FirstICell.Row = .Rows.Count Then Exit Do
ReDim Preserve RegionStartRows(1 To UBound(RegionStartRows) + 1)
RegionStartRows(UBound(RegionStartRows)) = FirstICell.Row
Loop
For X = 1 To UBound(RegionStartRows)
Set FirstICell = .Cells(RegionStartRows(X), "I")
FirstICell.Offset(1).Value = FirstICell.Offset(1).Value & " " & _
FirstICell.Offset(2).Value
FirstICell.Offset(3).Resize(2).Copy FirstICell.Offset(2)
FirstICell.Offset(4).Resize(2).Clear
Next
End With
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Do not use this code... it will not work correctly. I'll be back shortly
with working code.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Give this macro a try (again, I would do it to a COPY of your worksheet
first)...

Sub ManipulateColumnI()
Dim FirstICell As Range
Const StartOfDataRow As Long = 2
With Worksheets("Sheet6")
Set FirstICell = .Cells(StartOfDataRow, "I")
Do
FirstICell.Offset(1).Value = FirstICell.Offset(1).Value & " " & _
FirstICell.Offset(2).Value
FirstICell.Offset(3).Resize(2).Copy FirstICell.Offset(2)
FirstICell.Offset(4).Resize(2).Clear
Set FirstICell = FirstICell.End(xlDown).End(xlDown)
Loop While FirstICell.Row < .Rows.Count
End With
End Sub

Don't forget to adjust the Worksheet name in the With statement and the
start row of your data in Column I in the Const statement (for the
StartOfDataRow constant).

--
Rick (MVP - Excel)


"colwyn" wrote in message
...

In each series in column I, I want to:

1. combine every second and third cell.

2. delete those cells formatted blue.(this is the final entry in each
set of data)

3. move every fourth and fifth cells up one row.


1
red
1760
102.97
101.16
1.02



2
red
2200
133.97
128.51
1.04

3
green
2200
127.70
127.66
1.00


4
black
1320
74.84
73.45
1.02

5
red
1320
72.83
73.45
0.99


6
blue
1320
71.87
73.45
0.98





Here's how I want it:

1
red 1760
102.97
101.16





2
red 2200
133.97
128.51



3
green 2200
127.70
127.66




4
black 1320
74.84
73.45



5
red 1320
72.83
73.45




6
blue 1320
71.87
73.45


--
colwyn
------------------------------------------------------------------------
colwyn's Profile:
http://www.thecodecage.com/forumz/member.php?userid=34
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=41016