cell selection, merging.....
 

Ok, As stated in another post, I'm trying to make a macro for a selection of
cells based on a criteria.
In running it through, I'm finding that it selects increasingly larger cell
groupings.
I tried setting the variable to nothing, so as to "reset" it, for the
purpose of only grabbing the cells around which my criteria exists.

My core code is:

For Each rcell2 In Selection
If rcell2.Borders(xlEdgeBottom).LineStyle = xlSolid Then
ActiveSheet.Range(rcell1, rcell2).Select
With Selection
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
End With
'Set rcell1 = Nothing
'Set rcell2 = Nothing
ActiveCell.Offset(1, 0).Select
'end if
End If
Next rcell2

If I use the set ... = nothing, it emtpies the variable, resulting in my
outermost if test to fail-- I get an object or with block not set error.

So, I need some code to empty the buffer of the value for my rcell1
variable-- so I can still retain its being a selection, but not have it
remember the original cell location.
What code would I use to accomplish this?

Thank you.

cell selection, merging.....
 

First, I would drop the .select's

For Each rcell2 In Selection
If rcell2.Borders(xlEdgeBottom).LineStyle = xlSolid Then
with ActiveSheet.Range(rcell1, rcell2)
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
End With
'then maybe reset the rcell1
'maybe to the cell below the last rcell2
set rcell1 = rcell2.offset(0,1)
set rcell2 = nothing
end if
Next rcell2

But this is just a guess. I really don't know what rcell1 is used for.

Steve wrote:

Ok, As stated in another post, I'm trying to make a macro for a selection of
cells based on a criteria.
In running it through, I'm finding that it selects increasingly larger cell
groupings.
I tried setting the variable to nothing, so as to "reset" it, for the
purpose of only grabbing the cells around which my criteria exists.

My core code is:

For Each rcell2 In Selection
If rcell2.Borders(xlEdgeBottom).LineStyle = xlSolid Then
ActiveSheet.Range(rcell1, rcell2).Select
With Selection
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
End With
'Set rcell1 = Nothing
'Set rcell2 = Nothing
ActiveCell.Offset(1, 0).Select
'end if
End If
Next rcell2

If I use the set ... = nothing, it emtpies the variable, resulting in my
outermost if test to fail-- I get an object or with block not set error.

So, I need some code to empty the buffer of the value for my rcell1
variable-- so I can still retain its being a selection, but not have it
remember the original cell location.
What code would I use to accomplish this?

Thank you.

--

Dave Peterson