how can I change rows of data around by code ??

[colwyn]

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

 

[Rick Rothstein]

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

 

[colwyn]

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

 

[Rick Rothstein]

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

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.

 

[colwyn]

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'
trying to achieve. Please see it below.
Big thanks.
Colwyn

 

[colwyn]

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

 

[Rick Rothstein]

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

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?

 

[colwyn]

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

 

[Rick Rothstein]

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

Do not use this code... it will not work correctly. I'll be back shortly
with working code.

 

[Rick Rothstein]

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

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.