Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select block of rows w/data between blank rows
I am running the following macro to combine text from multiple rows into one
row in a specific column. (Thanks! T.Williams and Dmoney for helping me on this several days ago) Sub Combine() Dim c As Range Dim s As String For Each c In Selection s = s & IIf(s = "", "", Chr(10)) & c.Value Next c Selection.Cells.Value = "" Selection.Cells(1).Value = s End Sub Now, Id like to additional function to my macro. I would like the macro, again in a specific column, to find the first row that contains data, select it and any rows beneath it in the same column until it encounters a blank row (so the whole block is selected), run the above macro on the selected rows. Then, move on to find the next row (still in the same column) that contains data and run the process all over again. Is this possible? Thanks again. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select block of rows w/data between blank rows
Sub Combine1()
Dim rng as Range, rng1 as Range Dim ar as Range, c as Range Dim s as String set rng = Columns(Selection.Columns(1).Column) On Error Resume Next set rng1 = rng.specialcells(xlconstants) On Error goto 0 for each ar in rng1.Areas for each c in ar s = s & IIf(s = "", "", Chr(10)) & c.Value Next c ar.clearcontents ar(1).Value = s Next End Sub -- Regards, Tom Ogilvy "Janna" wrote in message ... I am running the following macro to combine text from multiple rows into one row in a specific column. (Thanks! T.Williams and Dmoney for helping me on this several days ago) Sub Combine() Dim c As Range Dim s As String For Each c In Selection s = s & IIf(s = "", "", Chr(10)) & c.Value Next c Selection.Cells.Value = "" Selection.Cells(1).Value = s End Sub Now, I'd like to additional function to my macro. I would like the macro, again in a specific column, to find the first row that contains data, select it and any rows beneath it in the same column until it encounters a blank row (so the whole block is selected), run the above macro on the selected rows. Then, move on to find the next row (still in the same column) that contains data and run the process all over again. Is this possible? Thanks again. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select block of rows w/data between blank rows
Tom,
The macro ran perfectly about halfway through my worksheet and then I get a run-time error '7': out of memory. When I click on Debug, it highlights the last line ar(1).Value = s Is my worksheet too large? Janna "Tom Ogilvy" wrote: Sub Combine1() Dim rng as Range, rng1 as Range Dim ar as Range, c as Range Dim s as String set rng = Columns(Selection.Columns(1).Column) On Error Resume Next set rng1 = rng.specialcells(xlconstants) On Error goto 0 for each ar in rng1.Areas for each c in ar s = s & IIf(s = "", "", Chr(10)) & c.Value Next c ar.clearcontents ar(1).Value = s Next End Sub -- Regards, Tom Ogilvy "Janna" wrote in message ... I am running the following macro to combine text from multiple rows into one row in a specific column. (Thanks! T.Williams and Dmoney for helping me on this several days ago) Sub Combine() Dim c As Range Dim s As String For Each c In Selection s = s & IIf(s = "", "", Chr(10)) & c.Value Next c Selection.Cells.Value = "" Selection.Cells(1).Value = s End Sub Now, I'd like to additional function to my macro. I would like the macro, again in a specific column, to find the first row that contains data, select it and any rows beneath it in the same column until it encounters a blank row (so the whole block is selected), run the above macro on the selected rows. Then, move on to find the next row (still in the same column) that contains data and run the process all over again. Is this possible? Thanks again. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select block of rows w/data between blank rows
Best I can recommend is to close excel, perhaps reboot windows, then open
excel and try it with only excel open. How many separate blocks of multiple rows do you have? -- Regards, Tom Ogilvy "Janna" wrote in message ... Tom, The macro ran perfectly about halfway through my worksheet and then I get a run-time error '7': out of memory. When I click on Debug, it highlights the last line ar(1).Value = s Is my worksheet too large? Janna "Tom Ogilvy" wrote: Sub Combine1() Dim rng as Range, rng1 as Range Dim ar as Range, c as Range Dim s as String set rng = Columns(Selection.Columns(1).Column) On Error Resume Next set rng1 = rng.specialcells(xlconstants) On Error goto 0 for each ar in rng1.Areas for each c in ar s = s & IIf(s = "", "", Chr(10)) & c.Value Next c ar.clearcontents ar(1).Value = s Next End Sub -- Regards, Tom Ogilvy "Janna" wrote in message ... I am running the following macro to combine text from multiple rows into one row in a specific column. (Thanks! T.Williams and Dmoney for helping me on this several days ago) Sub Combine() Dim c As Range Dim s As String For Each c In Selection s = s & IIf(s = "", "", Chr(10)) & c.Value Next c Selection.Cells.Value = "" Selection.Cells(1).Value = s End Sub Now, I'd like to additional function to my macro. I would like the macro, again in a specific column, to find the first row that contains data, select it and any rows beneath it in the same column until it encounters a blank row (so the whole block is selected), run the above macro on the selected rows. Then, move on to find the next row (still in the same column) that contains data and run the process all over again. Is this possible? Thanks again. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select block of rows w/data between blank rows
I have approximately 1000 separate blocks of data. I think I know why I'm
getting the memory error. When I use my original maco, manually selecting each block of rows in my column and then running the macro, it combines the data correctly into one row. (Then I manually select the second block of data, run the macro and it combines that data into one row) However, when I run the macro below, it takes the first block of rows and combines the data into one row (like I want), but when it moves down to the next block of rows, it inserts the first row from above, and then appends the 2nd block of data onto the first. For the third block of data, it puts the first and second rows and then appends the 3rd block of data onto it. In other words, as it moves through the worksheet, it's not treating each block of rows independently--which is probably why I run out of memory because as it processes the worksheet, the rows are getting huge :) Not sure what I'm doing wrong. Any thoughts? "Tom Ogilvy" wrote: Best I can recommend is to close excel, perhaps reboot windows, then open excel and try it with only excel open. How many separate blocks of multiple rows do you have? -- Regards, Tom Ogilvy "Janna" wrote in message ... Tom, The macro ran perfectly about halfway through my worksheet and then I get a run-time error '7': out of memory. When I click on Debug, it highlights the last line ar(1).Value = s Is my worksheet too large? Janna "Tom Ogilvy" wrote: Sub Combine1() Dim rng as Range, rng1 as Range Dim ar as Range, c as Range Dim s as String set rng = Columns(Selection.Columns(1).Column) On Error Resume Next set rng1 = rng.specialcells(xlconstants) On Error goto 0 for each ar in rng1.Areas for each c in ar s = s & IIf(s = "", "", Chr(10)) & c.Value Next c ar.clearcontents ar(1).Value = s Next End Sub -- Regards, Tom Ogilvy "Janna" wrote in message ... I am running the following macro to combine text from multiple rows into one row in a specific column. (Thanks! T.Williams and Dmoney for helping me on this several days ago) Sub Combine() Dim c As Range Dim s As String For Each c In Selection s = s & IIf(s = "", "", Chr(10)) & c.Value Next c Selection.Cells.Value = "" Selection.Cells(1).Value = s End Sub Now, I'd like to additional function to my macro. I would like the macro, again in a specific column, to find the first row that contains data, select it and any rows beneath it in the same column until it encounters a blank row (so the whole block is selected), run the above macro on the selected rows. Then, move on to find the next row (still in the same column) that contains data and run the process all over again. Is this possible? Thanks again. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select block of rows w/data between blank rows
That's my fault. Instead of writing my own complete routine, I did what you
said and combined it with yours. In doing that, I forgot to reset s. My bad. Sub Combine1() Dim rng as Range, rng1 as Range Dim ar as Range, c as Range Dim s as String set rng = Columns(Selection.Columns(1).Column) On Error Resume Next set rng1 = rng.specialcells(xlconstants) On Error goto 0 for each ar in rng1.Areas s = "" for each c in ar s = s & IIf(s = "", "", Chr(10)) & c.Value Next c ar.clearcontents ar(1).Value = s Next End Sub Should fix it. -- Regards, Tom Ogilvy "Janna" wrote in message ... I have approximately 1000 separate blocks of data. I think I know why I'm getting the memory error. When I use my original maco, manually selecting each block of rows in my column and then running the macro, it combines the data correctly into one row. (Then I manually select the second block of data, run the macro and it combines that data into one row) However, when I run the macro below, it takes the first block of rows and combines the data into one row (like I want), but when it moves down to the next block of rows, it inserts the first row from above, and then appends the 2nd block of data onto the first. For the third block of data, it puts the first and second rows and then appends the 3rd block of data onto it. In other words, as it moves through the worksheet, it's not treating each block of rows independently--which is probably why I run out of memory because as it processes the worksheet, the rows are getting huge :) Not sure what I'm doing wrong. Any thoughts? "Tom Ogilvy" wrote: Best I can recommend is to close excel, perhaps reboot windows, then open excel and try it with only excel open. How many separate blocks of multiple rows do you have? -- Regards, Tom Ogilvy "Janna" wrote in message ... Tom, The macro ran perfectly about halfway through my worksheet and then I get a run-time error '7': out of memory. When I click on Debug, it highlights the last line ar(1).Value = s Is my worksheet too large? Janna "Tom Ogilvy" wrote: Sub Combine1() Dim rng as Range, rng1 as Range Dim ar as Range, c as Range Dim s as String set rng = Columns(Selection.Columns(1).Column) On Error Resume Next set rng1 = rng.specialcells(xlconstants) On Error goto 0 for each ar in rng1.Areas for each c in ar s = s & IIf(s = "", "", Chr(10)) & c.Value Next c ar.clearcontents ar(1).Value = s Next End Sub -- Regards, Tom Ogilvy "Janna" wrote in message ... I am running the following macro to combine text from multiple rows into one row in a specific column. (Thanks! T.Williams and Dmoney for helping me on this several days ago) Sub Combine() Dim c As Range Dim s As String For Each c In Selection s = s & IIf(s = "", "", Chr(10)) & c.Value Next c Selection.Cells.Value = "" Selection.Cells(1).Value = s End Sub Now, I'd like to additional function to my macro. I would like the macro, again in a specific column, to find the first row that contains data, select it and any rows beneath it in the same column until it encounters a blank row (so the whole block is selected), run the above macro on the selected rows. Then, move on to find the next row (still in the same column) that contains data and run the process all over again. Is this possible? Thanks again. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
select block of rows w/data between blank rows
Thanks Tom. You guys are awesome.
"Tom Ogilvy" wrote: That's my fault. Instead of writing my own complete routine, I did what you said and combined it with yours. In doing that, I forgot to reset s. My bad. Sub Combine1() Dim rng as Range, rng1 as Range Dim ar as Range, c as Range Dim s as String set rng = Columns(Selection.Columns(1).Column) On Error Resume Next set rng1 = rng.specialcells(xlconstants) On Error goto 0 for each ar in rng1.Areas s = "" for each c in ar s = s & IIf(s = "", "", Chr(10)) & c.Value Next c ar.clearcontents ar(1).Value = s Next End Sub Should fix it. -- Regards, Tom Ogilvy "Janna" wrote in message ... I have approximately 1000 separate blocks of data. I think I know why I'm getting the memory error. When I use my original maco, manually selecting each block of rows in my column and then running the macro, it combines the data correctly into one row. (Then I manually select the second block of data, run the macro and it combines that data into one row) However, when I run the macro below, it takes the first block of rows and combines the data into one row (like I want), but when it moves down to the next block of rows, it inserts the first row from above, and then appends the 2nd block of data onto the first. For the third block of data, it puts the first and second rows and then appends the 3rd block of data onto it. In other words, as it moves through the worksheet, it's not treating each block of rows independently--which is probably why I run out of memory because as it processes the worksheet, the rows are getting huge :) Not sure what I'm doing wrong. Any thoughts? "Tom Ogilvy" wrote: Best I can recommend is to close excel, perhaps reboot windows, then open excel and try it with only excel open. How many separate blocks of multiple rows do you have? -- Regards, Tom Ogilvy "Janna" wrote in message ... Tom, The macro ran perfectly about halfway through my worksheet and then I get a run-time error '7': out of memory. When I click on Debug, it highlights the last line ar(1).Value = s Is my worksheet too large? Janna "Tom Ogilvy" wrote: Sub Combine1() Dim rng as Range, rng1 as Range Dim ar as Range, c as Range Dim s as String set rng = Columns(Selection.Columns(1).Column) On Error Resume Next set rng1 = rng.specialcells(xlconstants) On Error goto 0 for each ar in rng1.Areas for each c in ar s = s & IIf(s = "", "", Chr(10)) & c.Value Next c ar.clearcontents ar(1).Value = s Next End Sub -- Regards, Tom Ogilvy "Janna" wrote in message ... I am running the following macro to combine text from multiple rows into one row in a specific column. (Thanks! T.Williams and Dmoney for helping me on this several days ago) Sub Combine() Dim c As Range Dim s As String For Each c In Selection s = s & IIf(s = "", "", Chr(10)) & c.Value Next c Selection.Cells.Value = "" Selection.Cells(1).Value = s End Sub Now, I'd like to additional function to my macro. I would like the macro, again in a specific column, to find the first row that contains data, select it and any rows beneath it in the same column until it encounters a blank row (so the whole block is selected), run the above macro on the selected rows. Then, move on to find the next row (still in the same column) that contains data and run the process all over again. Is this possible? Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i randomly select 780 rows from 4000 rows of data | Excel Worksheet Functions | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Get number of rows that data uses, including blank rows | Excel Discussion (Misc queries) | |||
Get number of rows that data takes up, including blank rows | Excel Worksheet Functions |