The s/s is over 320000 rows deep and consists of over 40000 ranges of
data. Thus making manual changes impractical.

Ranges have between 6 and 30 rows each.
At the top of each rang, in column I, there are 6 rows of data that I
need to delete/change around.

I send a small attachment to give a better understanding of what I'm
trying to do.
If anyone has code to help me do this I would be most grateful.
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=39|
+-------------------------------------------------------------------+

--
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

Give this macro a try (on a COPY of your s/s first) and see if it does what
you want...

Sub DeleteTop6RowsInColIForEachRange()
Dim FirstICell As Range
Const StartOfDataRow As Long = 2
With Worksheets("Sheet5")
Set FirstICell = .Cells(StartOfDataRow, "I")
Do
FirstICell.Resize(6).Clear
Set FirstICell = FirstICell.End(xlDown).End(xlDown).End(xlDown)
Loop While FirstICell.Row < .Rows.Count
End With
End Sub

Note: Change the worksheet reference in the With statement to the worksheet
name where your data is located. Also change the StartOfDataRow to the first
row number containing your data in Colum I (which I assumed to be Row 2).

--
Rick (MVP - Excel)


"colwyn" wrote in message
...

The s/s is over 320000 rows deep and consists of over 40000 ranges of
data. Thus making manual changes impractical.

Ranges have between 6 and 30 rows each.
At the top of each rang, in column I, there are 6 rows of data that I
need to delete/change around.

I send a small attachment to give a better understanding of what I'm
trying to do.
If anyone has code to help me do this I would be most grateful.
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example.xls |
|Download:
http://www.thecodecage.com/forumz/attachment.php?attachmentid=39|
+-------------------------------------------------------------------+

--
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

Thanks Rick - but it doesn't do what needs be done.

What the macro does is delete every other series of data in column I.

See attachment for end product of your macro (included).
Big thanks.
Colwyn.


--
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

There is no attachment to your message (I don't think this newsgroups allows
them). Anyway, I realized the macro didn't do what you want (see my other
previous post in this thread); however, with respect to what you want, you
haven't really described to us what actually "needs to be done". Can you do
that for us? All you said was "delete/change around" which doesn't really
tell us anything.

--
Rick (MVP - Excel)


"colwyn" wrote in message
...

Thanks Rick - but it doesn't do what needs be done.

What the macro does is delete every other series of data in column I.

See attachment for end product of your macro (included).
Big thanks.
Colwyn.


--
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

My fault as I sent the post before the attachment - you should be able
to access it now.
Big thanks.
colwyn.


--
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

Nope... no attachment. I'm using Windows Mail to access the Microsoft news
server (as many volunteers here do) and I don't believe that interface
supports newsgroup attachments (or maybe it is the server that doesn't). Can
you post the spreadsheet to one of those sites that allows people to upload
files for free? Or can you describe in words what you want to do?

--
Rick (MVP - Excel)


"colwyn" wrote in message
...

My fault as I sent the post before the attachment - you should be able
to access it now.
Big thanks.
colwyn.


--
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

The code I gave you is probably not what you want. I just re-read your post
and noticed you said "delete/change around".. the code I gave you only
deletes the data in Column I (first 6 cells of each range). What did you
mean by "change around"? Also, did you want only Column I's data deleted
(and moved around), or were you talking about the entire first 6 rows of
each range?

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Give this macro a try (on a COPY of your s/s first) and see if it does
what you want...

Sub DeleteTop6RowsInColIForEachRange()
Dim FirstICell As Range
Const StartOfDataRow As Long = 2
With Worksheets("Sheet5")
Set FirstICell = .Cells(StartOfDataRow, "I")
Do
FirstICell.Resize(6).Clear
Set FirstICell = FirstICell.End(xlDown).End(xlDown).End(xlDown)
Loop While FirstICell.Row < .Rows.Count
End With
End Sub

Note: Change the worksheet reference in the With statement to the
worksheet name where your data is located. Also change the StartOfDataRow
to the first row number containing your data in Colum I (which I assumed
to be Row 2).

--
Rick (MVP - Excel)


"colwyn" wrote in message
...

The s/s is over 320000 rows deep and consists of over 40000 ranges of
data. Thus making manual changes impractical.

Ranges have between 6 and 30 rows each.
At the top of each rang, in column I, there are 6 rows of data that I
need to delete/change around.

I send a small attachment to give a better understanding of what I'm
trying to do.
If anyone has code to help me do this I would be most grateful.
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example.xls |
|Download:
http://www.thecodecage.com/forumz/attachment.php?attachmentid=39|
+-------------------------------------------------------------------+

--
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

Thanks Rick.
I only wish to change the data in Column I.
Have you looked at the attachment? Column L has details of what I'm
trying to achieve. Please see it below.
Big thanks.
Colwyn.


--
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

There is no attachment... I'm using Windows Mail to access the Microsoft
news server and I don't believe that interface supports attachments. Can you
copy/paste the details in Column L into your message?

--
Rick (MVP - Excel)


"colwyn" wrote in message
...

Thanks Rick.
I only wish to change the data in Column I.
Have you looked at the attachment? Column L has details of what I'm
trying to achieve. Please see it below.
Big thanks.
Colwyn.


--
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

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