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.

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