Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I change rows of data around by code ??
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code for moving data from even rows to columns after data in oddrows | Excel Programming | |||
Worksheet change code to colour in rows in a database | Excel Programming | |||
Need VBA code to dtermine how many rows have data in them | Excel Programming | |||
Macro to add rows at a change in data | Excel Programming | |||
VBA code to Autofill one cell to many rows below where row count will change | Excel Programming |