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